Foundations of locking - help

[James E. King, III]

Okay, I was under the assumption that commercial relational databases
allowed for multiple readers and a single writer. Unfortunately I am
unable to get this to work. I've been able to reproduce a very simple
test case with both SQL Server 2000 and SAP DB 7.3 which causes a
deadlock. I simplified it even more by using regular SQL in the SQL
Query Analyzer.

In a nutshell, I have a very simple table:

create table foo
(
[id] integer identity (1, 1) not null,
[stuff] varchar (64) null,

primary key clustered
(
[id]
)
)

I open two simultaneous connections to the database using the Query
Analyzer. In one window I run this script which is designed to clean
out the table, insert a single record, and then create a simulated
long-running update transaction on that record:

---
BEGIN TRANSACTION t1
use utest
DELETE FROM FOO
COMMIT TRANSACTION t1

BEGIN TRANSACTION t2
INSERT INTO FOO (stuff) VALUES ('p1')
COMMIT TRANSACTION t2

BEGIN TRANSACTION t3
DECLARE write_cursor CURSOR FOR SELECT * FROM foo WHERE stuff = 'p1'
FOR UPDATE OF stuff
OPEN write_cursor

FETCH NEXT FROM write_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE foo SET stuff = 'p1x' WHERE CURRENT OF write_cursor
FETCH NEXT FROM write_cursor
END

CLOSE write_cursor
DEALLOCATE write_cursor

WAITFOR DELAY '00:00:30'

COMMIT TRANSACTION t3
---

As you can see the delay of 30 seconds simulates a long transaction...
Now in the other window while the WAITFOR is waiting I run a script
that is supposed to be able to retrieve the value that used to be
there, because the transaction has not yet been committed:

---
BEGIN TRANSACTION t4
use utest
DECLARE read_cursor CURSOR FOR SELECT * FROM foo WHERE stuff = 'p1'
FOR READ ONLY
OPEN read_cursor

FETCH NEXT FROM read_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Found one.'
FETCH NEXT FROM read_cursor
END

CLOSE read_cursor
DEALLOCATE read_cursor
COMMIT TRANSACTION t4
---

To recap, what I'm trying to do is allow for the modification of a row
in a large transaction and still allow read-only queries to execute
and not block. Right now they are blocking. Here is the state of the
database locks when sitting in the waiting loop:

Object Lock Type Mode Status Owner Index Resource
utest DB S GRANT Sess
utest.dbo.foo PAG IX GRANT Xact PK__foo__76CBA758 1:77
utest.dbo.foo TAB IX GRANT Xact
utest.dbo.foo KEY X GRANT Xact PK__foo__76CBA758 (f600f6bd327e)

So as you can see the lock on the primary key is exclusive because of
the update, and as such no read operations can be performed. Now I
thought the purpose of these commercial databases was to provide a
mechanism where one user would write to a record, however while they
were in the middle of doing so other users could read from the old
record's value... in this particular example I would expect the second
block of code to find the value 'p1', however it does not.

If I change transaction isonation to uncommitted read, I can retrieve
the value 'p1x' without any blocking.

So my ultimate question is this: Is my premise fundamentally flawed,
or should database systems be able to allow non-blocking reading
during another transaction's update? SQL Server 2000 seems to have
the notion of a 'U' lock (Update) but the documentation says it'll
turn into an exclusive 'X' lock when the update actually occurs. An
exclusive update prevents reading. I don't like this at all.

Since both SQL Server 2000 and SAP DB exhibit this behavior, I'm
wondering if this is the way the world is and I need to accept it, or
if there are solutions to this seemingly simple and fundamental
problem apart from preventing 2 transactions from attempting to
operate (where one writes and any multitude read) on a single row.

Thank you for your help.

[Andrew J. Kelly]

That is the whole reason for blocking in the first place. You had the answer
yourself when you referred to the isolation level. If you have a
Read-Committed isolation level (which is the default) then you can NOT read
the value of a row that is being modified, period. That would defeat the
purpose. Would you want to read the value to make a decision based on that
value when you know it is in the process of being changed. If so then use
a different isolation but if this was important data I wouldn't want to be
the recipient of that report. If you read it with a Read-Uncommitted level
then you can make a decision based on the value when you read it, but that
transaction can be rolled back and you might still have flawed data. So the
choices are if you want consistent data you use one of the committed type
isolation levels and deal with the blocking or you read uncommitted and take
your chances. Oracle has a "feature" where they essentially do a snapshot
of the data when you start reading so you don't have this blocking but in
the end it is pretty much the same as above. The key is to not have a
transaction that lasts 30 seconds and your usually OK.

--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.

"James E. King, III" <jk...@ariessys.com> wrote in message
news:e94069f9.02052...@posting.google.com...

[Enigma Epiphany]

in the second process you run

set transaction isolation level read uncommitted

this will allow the cursor to access the table however the data has alread
been written (althoug uncommited).
change the criteria to read the data

-- DECLARE read_cursor CURSOR FOR SELECT * FROM foo WHERE stuff = 'p1x'

if in the end you rollback the simulate update. You will can read your
original data after the rollback.

-- DECLARE read_cursor CURSOR FOR SELECT * FROM foo WHERE stuff = 'p1'

"James E. King, III" <jk...@ariessys.com> wrote in message
news:e94069f9.02052...@posting.google.com...

[Simon Su (MS)]

Dear James,

The key concepts here are "Concurrency Problems" and "concurrency
control", Please review the following formal explanation,
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_3kqb.asp?frame=tr
ue
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_57p5.asp?frame=tr
ue

Also I think Andrew had provided an excellent explanation of this. Thank
you Andrew.

Regards,
Simon Su (MS)
-----------------------------
This posting is provided “AS IS” with no warranties, and confers no rights

--------------------
>Reply-To: "Andrew J. Kelly" <ake...@targitinteractive.com>
>From: "Andrew J. Kelly" <ake...@targitinteractive.com>
>References: <e94069f9.02052...@posting.google.com>
>Subject: Re: Foundations of locking - help

[James E. King, III]

Andrew,

Thanks for the explanation. I'm still having a tough time digesting
the news. Personally I feel that during a record update other
processes should still be able to do a non-blocking read and acquire
the original data (before the transaction that is updating the data
begins). Using marketing information from www.fresher.com it looks
like this database implements such a feature:

...
Versioning Engine
The Matisse server's unique versioning engine differentiates Matisse
from other database management systems. When an object is updated, the
versioning engine creates a new version of the object in a new
location, instead of updating the old version in place. Applications
are able to read the previous version of the object while the new
version is being created, and because all references are left intact,
applications see a consistent view of the database at all times. Once
the new version has been created, and the transaction is complete,
subsequent read requests are directed to the new version. If the
transaction fails, requests simply continue to be directed to the
previous version; there is no need to incur downtime by rolling back
the database in order to achieve a consistent state.
...

This is the kind of feature I would have expected in a relational
database. I guess this kind of functionality would be
application-specific. Anyway, thank you all for your speedy replies.

"Andrew J. Kelly" <ake...@targitinteractive.com> wrote in message news:<u2bASsQACHA.2524@tkmsftngp05>...

[Dean Thompson]

What you are looking for is often referred to as Snapshot Read
Isolation. This isolation level is offered by Oracle, among a few
others, but not SQL Server. Mr. Celko can address this better than I,
but I seem to recall that this isolation level was rejected for ANSI
SQL 92.

There are a considerable number of problems with Snapshot Read
Isolation. The most obvious problem is that of a financial system.
Let's say that a batch process was applying debits and credits bsed
upon periodic payments, for example Payroll. As this process occurrs,
someone performs a balance check and sees that there is $XX available.
With snapshot read isolation, that value would be wrong. (this was
the quickest explanation I could create in such a small area)

Your best choice is to avoid long running transactions like the
plague. If you have long running transactions, you are creating
problems for concurrency, problems with database logic, and risking
high quantities of deadlocks. It is bad all the way around.

HTH,
Dean

On 22 May 2002 04:50:40 -0700, jk...@ariessys.com (James E. King, III)
wrote:

[Andrew J. Kelly]

> Personally I feel that during a record update other
> processes should still be able to do a non-blocking read and acquire
> the original data (before the transaction that is updating the data
> begins).

There are very few real life needs for something like that since most people
care about accuracy in the data. The only real purpose I can think of at
the moment for a snapshot type of read is for reporting purposes. If you
wanted to run a report that reflected constantly changing data at a certain
point in time. You can easily mimic this snapshot effect by selecting off
the data into a temp table using a serializable isolation level while you
gather the data into the temp table(s). But for normal processing you can
get your self into real trouble if you can't rely on the accuracy of the
data and that is what you will get by doing those type reads. Even though I
find it un-necessary I will bet that SQL Server will eventually add this
feature into the system just to combat the fact that Oracle has this.

--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.

"James E. King, III" <jk...@ariessys.com> wrote in message
news:e94069f9.02052...@posting.google.com...

[Michael Cornelissen]

Dear Simon,

We were actually searching for the same problem as James
King was looking for.
Coming from using Oracle in the past we are switching to
SQL Server 2000. What I would want to drop in the
discussion is that if you read data from the database I
expect to see consistent i.e. committed data. Whether
there is a transaction going on is actually not relevant
for the read: in the end it is not committed yet!

What I like to know is: is it possible to do a select on a
table which is being modified by another transaction and
getting a result without the uncommitted changes of this
other transaction but the result set as I would have
received if this other transaction had not taken place?

Thanks in advance,

Michael Cornelissen
Dicon Development Center bv

>>> DECLARE write_Í{ wÀ ¾ oq, dgñ"$ºÃ
> ÙV cursor CURSOR FOR SELECT * FROM foo WHERE stuff = 'p1'

>.
>

[Vikrant V Dalwale [MS]]

Hello Michel ,

You can you the NOLOCK Hint for the SELECT statement , however, you will
get Uncommitted
records modified by the other transactions..! This is called Dirty Read.
There is no other way to
read the Uncommitted changes by other transaction due to transaction
isolation property of RDBMS.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.ｩ 2002 Microsoft Corporation. All rights
reserved.

Additional support can be obtained at http://support.microsoft.com

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

--------------------
>Content-Class: urn:content-classes:message
>From: "Michael Cornelissen" <mcorne...@dicon.nl>
>Sender: "Michael Cornelissen" <mcorne...@dicon.nl>
>References: <e94069f9.02052...@posting.google.com>
<u2bASsQACHA.2524@tkmsftngp05> <3zEMYCUACHA.1716@cpmsftngxa07>

>Subject: Re: Foundations of locking - help

>Date: Mon, 3 Jun 2002 08:41:14 -0700
>Lines: 260
>Message-ID: <56e301c20b15$18167960$9de62ecf@tkmsftngxs01>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: quoted-printable
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
>Thread-Index: AcILFRgW1DdX5J3TQbS6Bd2Wkkca2Q==
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: tkmsftngxs01 207.46.230.157
>Path: cpmsftngxa08!cpmsftngxa07!tkmsftngxs01
>Xref: cpmsftngxa08 microsoft.public.sqlserver.programming:263182
>X-Tomcat-NG: microsoft.public.sqlserver.programming

>>This posting is provided ｡ｰAS IS｡ｱ with no warranties,

>>>> DECLARE write_ﾍ{掫ﾀ ｾ oq, dg�$ｺﾃ
>> ﾙV cursor CURSOR FOR SELECT * FROM foo WHERE stuff = 'p1'

[Niels Berglund]

"Michael Cornelissen" <mcorne...@dicon.nl> wrote in news:56e301c20b15
$18167960$9de62ecf@tkmsftngxs01:

> What I like to know is: is it possible to do a select on a
> table which is being modified by another transaction and
> getting a result without the uncommitted changes of this
> other transaction but the result set as I would have
> received if this other transaction had not taken place?
>

I.e., you want to see the result the same way as Oracle does it, where you
basically get the old version of the data? If so, no, you can get the un-
committed data as per Vikrant's post, but I believe that wasn't what you
asked for.

Niels

--
***************************************
* Niels Berglund
* Developmentor
* nie...@develop.com
* http://staff.develop.com/nielsb/
***************************************

[Vikrant V Dalwale [MS]]

Hello ,

When a transaction is modifying a table, what resources its gonna Hold
the Lock on is completely
dependent on the ISOLATION_LEVEL, what data that transaction is gonna
modify etc..e.g. if a transaction is having a row level lock
to modify only that Row, then other transactions won't have any problem
seeing the table rows EXCEPT the row being modified
by that transaction.

Its hard to give the generalized answer for this question because of the
reasons I just mentioned above.

I would highly recommed you to go to MSDN library and search for SQL Server
Transaction and Locking which explains how
SQL Server handles locks and transactions.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.

You assume all risk for your use.© 2002 Microsoft Corporation. All rights
reserved.

Additional support can be obtained at http://support.microsoft.com

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

--------------------

>Subject: Re: Foundations of locking - help

>From: Niels Berglund <nie...@develop.com>
>References: <e94069f9.02052...@posting.google.com>
<u2bASsQACHA.2524@tkmsftngp05> <3zEMYCUACHA.1716@cpmsftngxa07>
<56e301c20b15$18167960$9de62ecf@tkmsftngxs01>
>Organization: Developmentor
>Message-ID: <Xns9222CE0714F1Eni...@207.46.230.185>
>User-Agent: Xnews/L5
>Newsgroups: microsoft.public.sqlserver.programming
>Date: Mon, 03 Jun 2002 12:14:31 -0700
>NNTP-Posting-Host: 195.224.94.226
>Lines: 1
>Path: cpmsftngxa07!cpmsftngxa10!tkmsftngp01!tkmsftngp02
>Xref: cpmsftngxa07 microsoft.public.sqlserver.programming:263101
>X-Tomcat-NG: microsoft.public.sqlserver.programming

[Niels Berglund]

vikr...@online.microsoft.com (Vikrant V Dalwale [MS]) wrote in
news:3Sc6nx1CCHA.1908@cpmsftngxa07:

> When a transaction is modifying a table, what resources its gonna
> Hold the Lock on is completely
> dependent on the ISOLATION_LEVEL, what data that transaction is gonna
> modify etc..e.g. if a transaction is having a row level lock
> to modify only that Row, then other transactions won't have any
> problem seeing the table rows EXCEPT the row being modified
> by that transaction.
>
> Its hard to give the generalized answer for this question because of
> the reasons I just mentioned above.
>
> I would highly recommed you to go to MSDN library and search for SQL
> Server Transaction and Locking which explains how
> SQL Server handles locks and transactions.
>

Thanks very much for pointing out MSDN and the tip about Transactions and
Locking but I can't see what that has to do with what the origianl
question was. I read the original question as: "Is there a way I can in a
select statement see the old/original values of records that from within
another session are being updated at the same time as I'm doing the
select".

You answered to that, that if you set the isolation level to uncommitted
you could see uncommitted changes - whis is correct, but didn't answer
the original question. I replied to the original question and said that
if he wanted the same fuctionality as he has in Oracle where you can see
the old version of the data that is being changed (i,e. the value before
changes) he can't do that in SQL Server (I assume you know how Oracle
handles transactions and locking and how it differs from SQL Server) -
but he can see uncommited changes as per your post.

However if I misunderstood the original question, I apologize.

[Michael Cornelissen]

Ok, Thank you both.
So, if it is not possible to see the 'old' data (which is
at that moment the only correct data) I understand the
need of keeping a transaction as short as possible.

In our case, we're developing a planning system whereby it
is normal to insert (or update) 1000 to 5000 records in
one go. Ofcourse this will take some time. I must now
accept the fact that other users cannot do any read
queries on the affected tables?

Do you have any suggestions in how to solve this problem?

Micheal

>.
>

[Dean Thompson]

Hi Michael,

If you think about it, during an insert, the only "correct" data is the data
that is not affected by the insert or update. You are correct that Snapshot
Read Isolation would allow you to query the entire table and writers would not
block readers. However, that data is not correct either because the data is
currently in a transaction. Whether you use Snapshot Read Isolation or Read
Uncommitted, either indicates poor design if you default to that transaction
level.

With Read Committed, you would see those records that are inserted once they are
committed, but you would still see all the other records. The important thing
is to keep your transactions as finite as possible in the records that they are
trying to view. If you need to view the records that are being updated, an
exclusive lock will be placed until the transaction is completed.

So, the answer to your first question in this part of the post is users can do
read queries on the tables. Just not read queries that affect the entire table
(unless you allow dirty reads which takes us back to the problem of potentially
incorrect data).

To answer the second question: Inserting/updating 1000 to 5000 records should
not take very long in human terms, just a few seconds at most. Avoid using
cursors, and perform the updates or inserts using sets of data instead. The
performance is typically much better with SQL Server (unlike Oracle, SQL Server
does not imply a cursor).

Don't mix in a bunch of unnecessary rowsets in you queries. If you know that a
transaction will affect only so many rows, then have your queries within that
transaction affect only those rows. Then, commit changes as often as is
accurately possible. This is the biggest decision you will make in designing an
affective and consistent database application.

If you need to, use scratch (#temp) tables to hold transient data until you are
ready to actually perform a large updates and inserts. Start the transaction
with a shared lock against the data within the table that will be updated so
that no one else may update the data, but can still read it. Then update your
scratch tables. Once you have all the information you need, perform the inserts
and updates to the production table and commit. This provides you with an
approximation of what you are wanting, but still allows other users to access
the production table and read "old" values. You may wish to actually use READ
SERIALIZABLE here so that the originating table does not allow inserts within
the initial range specified too, but it sounds like you may not be aware enough
of how finite your queries really should be.

HTH,
Dean

On Tue, 4 Jun 2002 01:29:25 -0700, "Michael Cornelissen" <mcorne...@dicon.nl>
wrote:

>Ok, Thank you both.
>So, if it is not possible to see the 'old' data (which is
>at that moment the only correct data) I understand the
>need of keeping a transaction as short as possible.
>
>In our case, we're developing a planning system whereby it
>is normal to insert (or update) 1000 to 5000 records in
>one go. Ofcourse this will take some time. I must now
>accept the fact that other users cannot do any read
>queries on the affected tables?
>
>Do you have any suggestions in how to solve this problem?
>
>Micheal
>

Dean Thompson <de...@txsqlusers.com>
Texas SQL Users <http://www.txsqlusers.com>
-------------------------------------------------
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can
be cut and pasted into Query Analyzer is appreciated.
-------------------------------------------------
Add your SQL Server or Developer website to our Web Resources directory at
http://www.txsqlusers.com/txLinks/add.asp
Sign up for our free monthly newsletter at
http://lb.bcentral.com/ex/manage/subscriberprefs.aspx?customerid=9828