On 4/10/2006 9:05 PM, Brett Elliott wrote:

> I'm new to Slony so please forgive my ignorance. I just got replication
> working between two machines and I'm pretty impressed with how easy that
> was.
> 
> Are there any links to wisdom regarding how I can avoid deadlock? From
> what I've read I should give table IDs in the order of hierarchy from
> general to specific. Do I need to be aware of which order I lock my
> tables, say I update two tables in one transaction or do a select for
> update on multiple tables, is that when I might need to be careful?

The locking order done by your application and the numbering of the 
tables is not important for normal runtime, as Slony does not acquire 
any locks at this time.

The deadlock problems can occur in the case you attempt to switchover or 
failover. At that moment, Slony needs to obtain an access exclusive lock 
on all the tables in the set. It is best to briefly stop your client 
application during this. We have seen reports in the past where some 
forgotten (by the application apparently) database connection which was 
IDLE in transaction delayed the switchover on the subscriber, which in 
one case got the DBA on duty into panic and he pulled the plugs by 
uninstalling Slony from the old origin (to get it back into writable 
mode). By doing so he lost all replicas and had to rebuild the entire 
cluster from scratch ... a postmaster restart on the other node would 
have done the job just fine and the switchover would have completed.

So as the Hitchhikers guide to the Galaxy says in big friendly letters 
"Do not panic".


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #