Re: Re : Row Level Share Locks

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 15 Sep 1998 19:50:10 GMT
Message-ID: <35ffc0f9.14161102@192.86.155.100>

A copy of this was sent to tjb839_at_0.0.0.0 (Tim Boemker) (if that email address didn't require changing) On 15 Sep 1998 18:10:05 GMT, you wrote:

>Why doesn't Oracle have row share locks? (Maybe an Oracle engineer
>could answer this one...)
>

I would ask why would you want them?

>Tim

Row Share Locks wouldn't make sense in Oracle.

A better question is "why don't other systems have Multi versioning and Read consistency?" or "Why would you want them as they can only decrease concurrency".

A row share lock is basically saying:

"Hey -- don't let anyone update this row, I'm not updating it and I don't want anyone else to update it. If I decide to update it, I might be able to -- but then again I might not, someone else may have placed one on it as well"

Row Share locks are used in other databases to provide consistent results for queries. For example if you had 3 rows in a table with the values:

1
2
3

Obviously the sum of the values is 1+2+3 = 6. In a system that didn't provide multi-versioning and read consistency as Oracle does the following can and will happen:

at time t1, user A reads row 1 (value = 1)
at time t2, user A moves off of row 1 and reads row 2 (value = 2)
at time t3, user B updates row 1 to have a value of 3 and row 3 to have a value

of 1
at time t4, user B commits
at time t5, user A reads the newly committed value of row 3 which is now 1 and comes up the with answer 1+2+1 = 4 -- A value that never existed in the database at any point in time.

In Oracle, given the above sequence we would see:

t1: user A reads row 1 (value = 1)
t2: user A moves off of row 1 and reads row 2 (value=2)
t3: user B updates row 1 to be 3 and row 3 to be 1
t4: user B commits
t5: user A reads row 3 -- discovers it was modified after their query began,

reconstructs row 3 as it existed at the beginning of the query and gets the answer of 3 for row 3 -- user A gets the answer 1+2+3 = 6

So, other systems implement shared read locks to give you a consistent read (sometimes referred to as 'repeatable read'). If they operated in repeatable read, they get a consistent answer however, concurrency goes down the tubes. Take that above example again with row level share locks

t1: user A reads row 1 (value =1) leaves it locked
t2: user A moves to row 2 (value = 2 )
t3: user B attempts to update row 1 and gets block
t4: user A moves to row 3 (value = 3 ) and gets the answer 6
t5: user A commits, releasing locks
t6: user B gets to continue the update

or maybe it happens like this:

t1: user A reads row 1, leaves it locked
t2: user A moves to row 2, locks it
t3: user B updates row 3, locking it
t4: user B attempts to update row 1 but cannot since A has a share lock on it
t5: user A attempts to read row 3 but cannot

at this point, the system will declare a deadlock and either A or B will lose and rollback allowing the other to continue.

If you are attempting to achive multi-statement consistent reads (another use of share read locks), you might consider looking at setting the isolation level to serializable. This will give you a multi-statement, read/write consistent view of the database without incurring any un-needed locks.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Sep 15 1998 - 14:50:10 CDT