Re: Trigger Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1999/06/28
Message-ID: <3778720d.1956182_at_newshost.us.oracle.com>#1/1


A copy of this was sent to "Joerg Leute" <leute_at_itdesign.de> (if that email address didn't require changing) On Sat, 26 Jun 1999 19:54:12 +0200, you wrote:

>True, but if you use the trigger to lock Table1, count it and then insert it
>into the other one would work
>
>LOCK TABLE TABLE IN EXCLUSIVE MODE NOWAIT;
>Count......
>Insert......
>Unlock it.....
>
>

I think thats exactly what I said below. "... unless you serialize access to table A based on your 'criteria'...".

The note I referenced shows a method that locks a much smaller set of rows then "all of them".

BTW: you cannot 'unlock' a table except by committing the transaction and the trigger cannot commit so this would serialize at the table level -- you would have only one insert/update/delete at a time on this table using lock table.

>
>>This is harder then it sounds. In a multi-user environment you will have
 some
>>issues unless you serialize access to table A based on your 'criteria'. If
 you
>>let 2 people simultaneously insert into A rows that match that
 'criteria' --
>>neither will see eachothers insert and may come to erroneous conclusions.
>>
>>See
>>
>
>

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon Jun 28 1999 - 00:00:00 CEST

Original text of this message