Re: Deadlock problem

Hi Chris (and Tommi, not to forget),

Chris Dawe wrote:
>
> On Mon, 18 Mar 2002 10:23:03 +0100, Ruud de Koter
> <ruud_dekoter_at_hp.com> wrote:
>
> >Hi Tommi,
> >
> >Tommi Korhonen wrote:
> >>
> >> I have a client program executing database operations like
> >>
> >> op1: newid := select max(id)+1 from mytable
> >> op2: insert into mytable values (newid,data)
> >>
> >> where op1 and op2 are executed inside the same transaction. If two clients are
> >> running in the same time, they generate transactions T1 and T2. If the
> >> isolation level of the database is set to serializable, it's possible to
> >> schedule operations like
> >>
-- skip ------
> >> which will lead to deadlock, since
> >>
-- skip ------
> >> (details taken from sybase, but the result was the same with all other
> >> databases tried)
> >>
> >> If the isolation level is lower than serializable, the deadlock is avoided, but
> >> both clients will get the same id-number (and that's not the purpose).
> >>
> >> How can I fix this? I would think this is no unique problem, op1+op2 must be a
> >> very basic operation in every client-server software. Of cource, DBMS can
> >> handle the deadlock by killing one of the transactions, but I would like to
> >> avoid the deadlock situation here altogether.
> >
> >One solution would be to construct an additional table, which stores the
> >max(id). You can then query this table, and update it with a new id value for
> >each operation. If you need to get the max(id) on more than one table, simply
> >add the table name to the new table's definition and query on the table name.
> >
> >This is probably faster also, as you don't need to determine the max(id) for
> >every action you perform.
> >
> >Locking problems on the new table are easier to prevent:
> >- lock rows exclusively, not the complete table (in Oracle I would use 'FOR
> >UPDATE OF'),
> >- make sure reading and updating the row is (part of) one transaction.
> >
> >As one transaction performs all actions before releasing the exclusive lock, no
> >risk exists that other transactions will start in the meantime. On catch: you
> >should keep the transaction as short as possible!
> >
> >Hope this gets you under way,
> >
> >Ruud.
>
> Hello Tommi,
>
> Ruud's suggestion of an "id" table is good for scalability - some
> DBMSs are not very good at optimising a MAX(id) from a table and
> actually get more data off disc than is really required.
>
> To minimise locking problems in the "id" table, change the order of
> the statements:
> UPDATE my_ids
> SET next_id = next_id+1
> WHERE category="my_table";
>
> SELECT new_id = next_id-1
> FROM my_ids
> WHERE category="my_table";
> [a]
> INSERT INTO my_table (id, data)
> VALUES (:new_id, :data);
> COMMIT;
>

Interesting suggestion. Never looked at it that way. I would emphasize, however, that it is essential to have a transaction start with the first statement. This will probably work out right if the isolation level is serializable, but in my view it never hurts to be explicit.

• skip ----

Good ridance,

Ruud.

Ruud de Koter                    HP OpenView Software Business Unit
Senior Software Engineer         IT Service Management Operation
Telephone: +31 (20) 514 15 89    Van Diemenstraat 200  
Telefax  : +31 (20) 514 15 90    PO Box 831
Telnet   : 547 - 1589            1000 AV  Amsterdam, the Netherlands

internet: http://www.openview.hp.com/itsm
          http://www.openview.hp.com/assetview
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------