Erik Jones wrote:
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?
I should also note that each of those updates occurs in it's own
transactions, but that they do not attempt to modify any other rows in
that table before commiting. They do, however, delete rows in another
common table (where they pulled the stat counts from), but the rows they
delete are disjunct.
The whole process/algorithm is such:
1. Get rows matching X from temp table.
2. Accumulate values from X and update row and field corresponding to X
in stats table.
3. Delete rows collected in step one.
4. Commit.
5. Repeat from step 1.
With multiple processes using the same algo and tables but for different
values of X.
--
erik jones <erik@xxxxxxxxxx>
software development
emma(r)
