Lock partitions
in [PgSql]
|
From: Tom Lane on 14 Sep 2006 15:32 Mark Wong <markw(a)osdl.org> writes: > Tom Lane wrote: >> With that change, I didn't see run_workload report any errors, but maybe >> I don't know where to look. > The error is captured in dbt2/scripts/output/*/client/error.log, where * > is the run directory. Hm ... here's what I see in there: Thu Sep 14 15:19:16 2006 tid:-1430387232 client.c:129 20 DB worker threads have started Thu Sep 14 15:19:31 2006 tid:1087957312 libpq/dbc_new_order.c:111 ERROR: deadlock detected DETAIL: Process 5334 waits for ShareLock on transaction 3505055; blocked by process 5363. Process 5363 waits for ShareLock on transaction 3505049; blocked by process 5334. CONTEXT: SQL statement "UPDATE stock SET s_quantity = s_quantity - 10 WHERE s_i_id = 48368 AND s_w_id = 1" Thu Sep 14 15:21:18 2006 tid:1089960256 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1089689920 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090636096 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090230592 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090365760 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090095424 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1089825088 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090500928 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090906432 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090771264 client_interface.c:33 socket closed on _receive Is the deadlock failure expected? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
From: Mark Wong on 14 Sep 2006 15:45 Tom Lane wrote: > Mark Wong <markw(a)osdl.org> writes: >> Tom Lane wrote: >>> With that change, I didn't see run_workload report any errors, but maybe >>> I don't know where to look. > >> The error is captured in dbt2/scripts/output/*/client/error.log, where * >> is the run directory. > > Hm ... here's what I see in there: > > Thu Sep 14 15:19:16 2006 > tid:-1430387232 client.c:129 > 20 DB worker threads have started > Thu Sep 14 15:19:31 2006 > tid:1087957312 libpq/dbc_new_order.c:111 > ERROR: deadlock detected > DETAIL: Process 5334 waits for ShareLock on transaction 3505055; blocked by process 5363. > Process 5363 waits for ShareLock on transaction 3505049; blocked by process 5334. > CONTEXT: SQL statement "UPDATE stock > SET s_quantity = s_quantity - 10 > WHERE s_i_id = 48368 > AND s_w_id = 1" [snip] > > Is the deadlock failure expected? Ooh, that's interesting. Deadlock failure is possible although I think we would all prefer that it didn't happen. In the scheme of the workload having failed transactions is ok. So with respect to having an invalid test run it's something I wouldn't worry about too much if it's infrequent. Mark ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
From: Tom Lane on 14 Sep 2006 19:45 Mark Wong <markw(a)osdl.org> writes: > Curious, I'm still seeing the same behavior. Maybe I'll take another > snapshot from CVS. Hm, maybe I need to try a bit harder here. Does the "not registered" error happen immediately/reliably for you, or do you need to run the test awhile? > As for the deadlock issue you mention I've been told > I have some seeding/random number generation problems in the kit. > Perhaps that is related to the deadlock at least. The nature of the locks suggests that you have two transactions trying to update the same two rows in opposite orders. The usually recommended fix is to ensure you use a consistent processing order within a transaction (eg, ascending primary keys --- but any well-defined row ordering will work). regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
From: markw on 14 Sep 2006 20:32 > Mark Wong <markw(a)osdl.org> writes: >> Curious, I'm still seeing the same behavior. Maybe I'll take another >> snapshot from CVS. > > Hm, maybe I need to try a bit harder here. Does the "not registered" > error happen immediately/reliably for you, or do you need to run the > test awhile? It appears to occur for every order status transaction. You can monitor the transactions by watching dbt2/scripts/output/*/driver/mix.log. A 'o' (lowercase) indicates a successful order status transaction while a 'O' (uppercase) indiciates an unsuccessful transaction. or, perhaps a simplier way is to start the database then: cd dbt2/src ../transaction_test -d localhost -l 5432 -t o That will connect directly to the database and execute an order status transaction. Thanks, Mark ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: Mark Wong on 19 Sep 2006 16:32
Tom Lane wrote: > Mark Wong <markw(a)osdl.org> writes: >> Curious, I'm still seeing the same behavior. Maybe I'll take another >> snapshot from CVS. > > Hm, maybe I need to try a bit harder here. Does the "not registered" > error happen immediately/reliably for you, or do you need to run the > test awhile? I did a gross test and my kit appears broken between the 8.0 and 8.1 releases. I'll try to narrow down the exact date. Mark ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster |