yesterday, tom lane committed another great feature. actually it's so great, that i really wonder how could i live without it earlier.
patch came from itagaki takahiro, and was heavily modified by tom. what it does?
according to commit log:
Log Message: ----------- Report the current queries of all backends involved in a deadlock (if they'd be visible to the current user in pg_stat_activity). This might look like it's subject to race conditions, but it's actually pretty safe because at the time DeadLockReport() is constructing the report, we haven't yet aborted our transaction and so we can expect that everyone else involved in the deadlock is still blocked on some lock. (There are corner cases where that might not be true, such as a statement timeout triggering in another backend before we finish reporting; but at worst we'd report a misleading activity string, so it seems acceptable considering the usefulness of reporting the queries.) Original patch by Itagaki Takahiro, heavily modified by me.
now. how does it look in real-life usage scenario?
consider this table:
# \d test Table "public.test" Column | Type | Modifiers --------+---------+--------------------------------------------------- id | integer | not null default nextval('test_id_seq'::regclass) x | text | Indexes: "test_pkey" PRIMARY KEY, btree (id)
with this content:
# SELECT * FROM test; id | x ----+--- 1 | a 2 | b (2 ROWS)
now, we have 2 sessions which try to modify the table at the same time:
|step||session 1||session 2|
|3.||update test set x = ‘c' where x = ‘a';|
|4.||update test set x = ‘d' where x = ‘b';|
|5.||update test set x = ‘f' where x = ‘b';|
|6.||update test set x = ‘g' where x = ‘a';|
session 1, after getting command (update) will hang, waiting for lock.
but when i enter final, 6th command in session 2, i will get this output:
ERROR: deadlock detected DETAIL: Process 13370 waits for ShareLock on transaction 5301136; blocked by process 13284. Process 13284 waits for ShareLock on transaction 5301137; blocked by process 13370.
now. it certainly has some level of details, but not really helpful details.
but with this new patch, situation looks differently:
ERROR: deadlock detected DETAIL: Process 26283 waits FOR ShareLock ON TRANSACTION 1648; blocked BY process 26275. Process 26275 waits FOR ShareLock ON TRANSACTION 1649; blocked BY process 26283. CONTEXT: Process 26283: UPDATE test SET x = 'g' WHERE x = 'a'; Process 26275: UPDATE test SET x = 'f' WHERE x = 'b';
now the error message shows both queries. these queries are not actually locking each other, but having them together will definitely help you pinpoint the part of your code which generates deadlocks.
another great feature 🙂