Postgresql – Deadlock during Slony uninstall and concurrent access of DB

deadlockpostgresql

I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process.

I was trying to uninstall node from a cluster, as per slony's documents this requires access exclusive locks on all replicated tables. When the slony process was uninstalling the cluster, at the same time the application inserted a row in one of the replicated tables which I think resulted in a dead lock:

DETAIL: Process 22593 waits for AccessExclusiveLock on relation 16392 of database 16388; blocked by process 11808.
Process 11808 waits for AccessShareLock on relation 18366 of database 16388; blocked by
process 22593.

Can any body confirm that what I am thinking is correct and suggest a solution for this.

Best Answer

I suspect you are going to have to disable application access during the uninstall process. The basic problem is that Slony uses large numbers of triggers to maintain the binary logs for shipping. These triggers are going to be hard to delete while there is concurrent access.

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues] 
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table. Another thread is then taking an IX on the issue table (which is blocked). This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.

Sql-server – All-or-none exclusive lock on 2 SQL tables

No, locks are acquired one-after-the-other because the number of locks is generally unpredictable. It might change with row count. This could work differently, but it doesn't.

Your locking technique basically doesn't work. The best workaround I can think of is to write a retry-loop which tries to acquire both locks and retries on deadlock.

Best Answer

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

Sql-server – All-or-none exclusive lock on 2 SQL tables

Related Question