Thread: Confused on a some deadlocks occuring...
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@myemma.com> software development emma (r)
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@myemma.com> software development emma(r)
I seem to get this error "user postgres could not be created, user account already exists" when trying to install postgres. Anyone knows how to fix this? Erik Jones wrote: > 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. > >
Gibson wrote:
> I seem to get this error "user postgres could not be created, user
> account already exists" when trying to install postgres. Anyone knows
> how to fix this?
Sounds to me like the user postgres on the OS already exists.
Joshua D. Drake
>
> Erik Jones wrote:
>> 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.
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
How do i remove this user?I am running on Win XP here. Joshua D. Drake wrote: > Gibson wrote: >> I seem to get this error "user postgres could not be created, user >> account already exists" when trying to install postgres. Anyone knows >> how to fix this? > > Sounds to me like the user postgres on the OS already exists. > > Joshua D. Drake > > >
Go to Start/ Control Panel / Administrative tools / Computer Management / and you see Local Users and groups Delete the user from there ----- Original Message ----- From: "Gibson" <gibson@nexgenstudio.com> Cc: "pgsql general" <pgsql-general@postgresql.org> Sent: Friday, September 01, 2006 2:31 AM Subject: Re: [GENERAL] Fatal error while installing > How do i remove this user?I am running on Win XP here. > > Joshua D. Drake wrote: >> Gibson wrote: >>> I seem to get this error "user postgres could not be created, user >>> account already exists" when trying to install postgres. Anyone knows >>> how to fix this? >> >> Sounds to me like the user postgres on the OS already exists. >> >> Joshua D. Drake >> >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.405 / Virus Database: 268.11.7/434 - Release Date: 30/08/2006 > >