| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.theory -> 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.
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 NetherlandsEmail : ruud_dekoter_at_hp.com
internet: http://www.openview.hp.com/itsm http://www.openview.hp.com/assetview intranet: http://ovweb.bbn.hp.com/itservicemanager --------------------------------------------------------------------------------------Received on Tue Mar 19 2002 - 02:00:31 CST
 |
 |