Ok, consider the following table definition:
CREATE TABLE stats (
id SERIAL PRIMARY KEY,
hits bigint default 0,
clickthrus bigint default 0,
referrals bigint default 0);
Now, this table has a lot of rows that are constantly being updated by
queries of the following form:
UPDATE stats
SET clickthrus = clickthrus + #
WHERE id = blah; -- sub various values for # and blah
There can be, and often are, multiple updates for the same row coming
in at the same time, but never for the same field. My understanding of
the locking involved is that updates take out row-exclusive locks to
prevent other transactions from modifying the data and to serialize with
other updates. So, multiple update statements to the same row come in,
the first to arrive is granted a row-exclusive lock and the other wait.
When the first is finished and commits, the second to have arrived get
the lock, and so forth. Here is what I am seeing all through my logs:
2006-08-29 03:17:25 CDT 16074 192.168.1.173(35190):STATEMENT: ABORT
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):ERROR: deadlock detected
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):DETAIL: Process 8553
waits for ShareLock on transaction 1548224183; blocked by process 5499.
Process 5499 waits for ShareLock on transaction 1548224182; blocked by
process 8553.
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):STATEMENT: UPDATE stats
SET hits = hits + 3
WHERE id = 271524;
or,
2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):ERROR: deadlock detected
2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):DETAIL: Process 12479
waits for ExclusiveLock on tuple (3024,45) of relation 33942 of database
33325; blocked by process 12513
.
Process 12513 waits for ShareLock on transaction 1550567046; blocked by
process 12495.
Process 12495 waits for ShareLock on transaction 1550569729; blocked by
process 12479.
2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):STATEMENT: UPDATE stats
SET click_thrus = clickthrus + 1
WHERE id = 275359;
What's with ShareLock s on transactions? Where do those come from?
--
erik jones <erik@xxxxxxxxxx>
software development
emma (r)
