Re: Debugging deadlocks

On Wed, Mar 30, 2005 at 02:30:58PM -0700, Michael Fuhr wrote:

> I think Alvaro is working on a new locking mechanism that will allow
> transactions to prevent a record from being modified without blocking
> other transactions doing the same.

Yeah, and it does work. (I posted the patch two days ago.)

alvherre=# create table a (a serial primary key);
NOTICE: CREATE TABLE creará una secuencia implícita «a_a_seq» para la columna serial «a.a»
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «a_pkey» para la tabla «a»
CREATE TABLE
alvherre=# create table b (a int references a);
CREATE TABLE
alvherre=# insert into a default values;
INSERT 0 1
alvherre=# insert into a default values;
INSERT 0 1
alvherre=# begin;
BEGIN
alvherre=# insert into b values (1);
INSERT 0 1

Session 2:
alvherre=# begin;
BEGIN
alvherre=# insert into b values (2);
INSERT 0 1
alvherre=# insert into b values (1);
INSERT 0 1

Session 1:
lvherre=# insert into b values (2);
INSERT 0 1
alvherre=# commit;
COMMIT

Session 2:
alvherre=# commit;
COMMIT

You'll notice if you do that on any released version it will deadlock ...

Now this can't be applied right away because it's easy to run "out of
memory" (shared memory for the lock table). Say, a delete or update
that touches 10000 tuples does not work. I'm currently working on a
proposal to allow the lock table to spill to disk ...

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"La persona que no quería pecar / estaba obligada a sentarse
en duras y empinadas sillas / desprovistas, por cierto
de blandos atenuantes" (Patricio Vogel)