On 3/24/2010 5:58 PM, michael at aers.ca wrote:
> We suffered a deadlock at one point today and I’m looking for some 
> advice on how to prevent it from happening again. In the postgres log I 
> found the following:

One of the tables seems to be sl_event. What is the other one?


Jan

> 
>  
> 
> ERROR:  deadlock detected
> 
> DETAIL:  Process 20230 waits for ExclusiveLock on relation 31152 of 
> database 26523; blocked by process 27297.
> 
>         Process 27297 waits for AccessExclusiveLock on relation 31205 of 
> database 26523; blocked by process 20230.
> 
>         Process 20230: select "_payments_cluster".ddlScript_prepare(1, -1);
> 
>         Process 27297: select "_payments_cluster".cleanupEvent('10 
> minutes'::interval, 'false'::boolean);
> 
> HINT:  See server log for query details.
> 
> CONTEXT:  SQL statement "LOCK TABLE _payments_cluster.sl_event IN 
> EXCLUSIVE MODE; INSERT INTO _payments_cluster.sl_event (ev_origin, 
> ev_seqno, ev_timestamp, ev_snapshot, ev_type, ev_data1, ev_data2, 
> ev_data3, ev_data4, ev_data5, ev_data6, ev_data7, ev_data8) VALUES ('1', 
> nextval('_payments_cluster.sl_event_seq'), now(), 
> "pg_catalog".txid_current_snapshot(), $1, $2, $3, $4, $5, $6, $7, $8, 
> $9); SELECT currval('_payments_cluster.sl_event_seq');"
> 
>         SQL statement "SELECT  
> "_payments_cluster".createEvent('_payments_cluster', 'SYNC', NULL)"
> 
>         PL/pgSQL function "ddlscript_prepare" line 29 at PERFORM
> 
> STATEMENT:  select "_payments_cluster".ddlScript_prepare(1, -1);
> 
>  
> 
> So it looks to me like the issue was caused by a ddl script running at 
> the same time as a slony cleanupEvent command. Our system requires some 
> dynamic changes to our db structure (in the form of new partitions and 
> things like that) these ddl scripts could run at any time. So, how can I 
> best manage this around the cleanupEvents?
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general


-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin