Transaction with Read Committed Isolation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction with Read Committed Isolation

Author

Topic

digital3
Starting Member

7 Posts

Posted - 2005-08-21 : 08:34:25

I have a query about concurrency and transactions with READ COMMITTED isolation.

If I have a simple theoretical table defined as
MyTable(ID int, Name varchar(64))
where ID is an IDENTITY column, managed by the DBMS.

Here's a stored procedure that inserts a row into that table, and returns the ID of the row inserted (in theory):

1. BEGIN TRANSACTION
2. INSERT INTO dbo.MyTable(Name) VALUES('Test')
3. SELECT MAX(ID) FROM dbo.MyTable
4. COMMIT TRANSACTION

By default, as I understand it, SQL Server will use READ COMMITTED isolation levels on commands unless explicitly told otherwise, and that READ COMMITTED provides an (effective) snapshot on a per-command basis, and not on a per-transaction basis (SERIALIZABLE isolation provides the latter). Since this stored proc doesn't specify the isolation level, it gets the default.

If I get this bit wrong, please correct me: I understand that a lock is granted for line 2, the INSERT proceeds and during that execution, and SQL server ensures a consistent snapshot is available to that executing command, but not before or after. This particular lock is released after the insert, but before line 3 executes. In theory, any other database connection could then insert another row before line 3 executes (say, if it had been blocked until line 2 of this stored proc finished). This transaction will see that committed row, and this will alter the outcome of the MAX(ID) statement on line 3 to return an ID which is not that of the row inserted in line 2. Is this a phantom row scenario?

If this is true, is there any real reason to put this SQL in a transaction with this isolation level?

Of course, I know I should be using @@Identity to get the last inserted row ID, but I'm more interested in the principle here of whether using BEGIN TRANSACTION at the top of a stored procedure (without specifying an isolation level) actually protects you from concurrent changes between commands.

So, if it doesn't protect you, would the only useful thing for the use of a transaction with this isolation be to delay the committal of changes until a specific point (or to permit rollbacks)?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-21 : 18:24:54

Good questions digital3, you've got a pretty good handle the concepts.

Let me try to (indirectly) answer some of your questions by making a couple points. Typical use for explicit transaction control is to insure an "all or nothing" type thing happens. ie: if you want to write changes to 3 seperate tables but only if all three changes were successfull. Though you can use explicit transactions to protect against concurrent changes, you need to be carefull to avoid excessive blocking and deadlocking. Since your transaction has 2 seperate operations that require locks on the same table, you've presented a likely scenario for a deadlock. (be sure to read the Books Online topic "minimizing deadlocks")

Sql server is very good at deciding what isolation level to use. The times when you would want to change the default is usually limited to when you don't want your reading of data to hold locks that prevent the writing of data. In those cases when you don't mind "dirty" reads, you can use READ UNCOMMITTED isolation level for your reads. That obviously would NOT apply to your "select max(id)" statement.

The ideal way to handle your sample code is to NOT use an explicit transaction and use the scope_identity() function to get your identity value. (again see BOL for details)


INSERT INTO dbo.MyTable(Name) VALUES('Test')
SELECT scope_identity()

On a related topic, depending on your application architecture, another way to reduce concurrency issues is to put all your sql code in stored procedures so that the time your transactions are open is not extened by round trips to the server. There are other advantages to using store procedures for all database access including ease of optimization and maintanance, as well as tighter security.

Hope this helps

Be One with the Optimizer
TG

digital3
Starting Member

7 Posts

Posted - 2005-08-21 : 19:39:57

Cheers TG, I hadn't considered that stored proc as a candidate for deadlocking, but I can see how it could be now.

I have changed it to use scope_identity(). My larger concern is that I know of several developers (myself included) who use transactions as if they were like the

lock(handle) {
do stuff
}

structure in languages like C# where it is always assumed the transaction automatically protects the enclosed statements from changes by concurrent queries. In these cases, it appears it is wrongly being used as a protective measure, rather than as a mechanism to achieve "all or nothing" commits.

So, just to confirm my understanding here, if I have a table like
MyTable(ID int, Total int), say with one row of values (1, 1000).

and have this stored procedure

1. BEGIN TRANSACTION
2. UPDATE dbo.MyTable SET dbo.MyTable.Total = dbo.MyTable.Total + 1000 WHERE dbo.MyTable.ID = 1
3. UPDATE dbo.MyTable SET dbo.MyTable.Total = dbo.MyTable.Total - 1000 WHERE dbo.MyTable.ID = 1
4. COMMIT TRANSACTION

With READ COMMITTED isolation, is it possible that another query ran between line 2 and 3 that set a value of 5000 for the ID=1 row, and committed that change, effectively meaning that when line 4 completes, the state of the row will be a total of 4000 (instead of 1000)?

Hopefully I'm wrong, or there's a few stored procedures I've written that might need a solid review.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-21 : 20:33:02

Your transaction will protect you from that situation. The "another" transaction will wait (be blocked actually) until your explicit transaction releases the lock (COMMITs) before setting the value to 5000. So in the end, the value will be 5000 though monentarily the committed transaction set the value back to 1.

Here, see for yourself:


create table myTable (ID int, total int)
go
insert myTable values (1,1000)
go

--exec this in one query window
BEGIN TRANSACTION
UPDATE dbo.MyTable SET dbo.MyTable.Total = dbo.MyTable.Total + 1000 WHERE dbo.MyTable.ID = 1
waitfor delay '00:00:05'
UPDATE dbo.MyTable SET dbo.MyTable.Total = dbo.MyTable.Total - 1000 WHERE dbo.MyTable.ID = 1
COMMIT TRANSACTION

--while the waitfor is waiting 5 seconds execute this in another query window
select * from myTable
UPDATE dbo.MyTable SET dbo.MyTable.Total = 5000 WHERE dbo.MyTable.ID = 1
select * from myTable

go
drop table myTable

Be One with the Optimizer
TG

digital3
Starting Member

7 Posts

Posted - 2005-08-21 : 21:17:06

Thanks for the demo code, TG.
It did solve one issue, but the code I tried below was not protected:

create table myTable (ID int, total int)
go

--exec this in one query window
BEGIN TRANSACTION
insert myTable values (1,1000)
waitfor delay '00:00:05'
select * from mytable
COMMIT TRANSACTION

go
drop table myTable

--while the waitfor is waiting 5 seconds execute this in another query window
insert myTable values (1,1000)
insert myTable values (2,1000)
insert myTable values (3,1000)

The query on the other connection was not blocked at all, and the select * from mytable inside the transaction was able to see these committed changes.

This is somewhat consistent with what I read here (Section 12.2.1) regarding READ COMMITTED isolation:

quote:
In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run. Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT.

Even though those notes are specific to PostgreSQL, I would assume MSSQLServer is the same.

So it seems that there probably are issues with some of my INSERT stored procedures regarding concurrency and I'll have to go back and review that.

I've never used the waitfor command before... should prove useful in diagnosing potential deadlock scenarios.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-21 : 22:57:17

well sure, inserting single records usually won't block other sessions from inserting single records. (especially on a table with no constraints). An explicit transaction won't prevent other sessions from performing inserts. So as far as sql server is concerned there's nothing to "protect" and concurrent users can insert to their hearts content. Try adding a primary key to myTable. Now the second window will be blocked and then generate a primary key violation.

create table myTable (ID int primary key, total int)

If you have any doubts about your strategy, I would encourage you to post a typical procedure. I'm sure you'll get a lot of feedback from sql team contributors. Some of it may even be good ;-)

Be One with the Optimizer
TG

digital3
Starting Member

7 Posts

Posted - 2005-08-21 : 23:20:59

Cool, makes perfect sense.

Cheers for your help on this issue.
I'll post back if I have any other issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources