April 19, 2012 at 4:21 am
The more I read on Isolation levels the more I get confused
Connection 1:
BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
-- Start Connection 2
SELECT * FROM T;
COMMIT;
----------------------------------
Connection 2:
Update table T ... -- not within a transaction
In above senario both Read commited and Rean uncommited Isolations will behave in the same way. 2nd select in Connection 1 will show updates from Connection 2 and hence will be different from Connection 1. Then how are the 2 different??
I know that read uncommited will read uncommited data as well . Here is second senario
Connection 1:
BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
-- Start Connection 2SELECT * FROM T;
COMMIT;
----------------------------------
Connection 2:
Begin Transaction -- Within Transaction
Update table T ...
WAITFOR DELAY '00:01:00'
COMMIT;
Above case will work like this
Read uncommited: 2nd select in connection 1 will display Update of Connection 2 even though its not commited yet.
Read Commited : 2nd select in connection 1 will not display Update of Connection 2 since its not commited yet. It will wait f
Does all this make sense??
April 19, 2012 at 7:43 am
Select statements aren't really a good way to test isolation levels. Even in Read Committed, all they take is shared locks, which means two Select statements don't interfere with each other, regardless of isolation level.
A better way to test them is to wrap an insert, update, or delete, and a select in a transaction, from connection 1. Don't commit or rollback the transaction. Then open connection 2, and run a select only. Don't worry about "commit" in connection 2, since it won't matter. (By the way, the error from just having "commit" is that begin and end transactions are connection-specific, so you can't have "begin transaction" in connection 1 and "commit" in connection 2.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2012 at 8:02 am
In above senario both Read commited and Rean uncommited Isolations will behave in the same way.
No. Read committed will read the committed transactions, read uncommitted will read uncommitted and committed as well. How do they behave the same way? They may have the same result in the above scenario, but that's because you have no uncommitted transactions.
Connection 1:
BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
-- Start Connection 2
SELECT * FROM T;
COMMIT;
From BOL: "BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent." "Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement." So no "transaction" here...
----------------------------------
Connection 2:
Begin Transaction -- Within Transaction
Update table T ...
WAITFOR DELAY '00:01:00'
COMMIT;
Above case will work like this
Read uncommited: 2nd select in connection 1 will display Update of Connection 2 even though its not commited yet.
Read Commited : 2nd select in connection 1 will not display Update of Connection 2 since its not commited yet. It will wait f
Yes.
So what are you confused about?
Jared
CE - Microsoft
April 19, 2012 at 9:36 am
SQLKnowItAll (4/19/2012)
In above senario both Read commited and Rean uncommited Isolations will behave in the same way.
No. Read committed will read the committed transactions, read uncommitted will read uncommitted and committed as well. How do they behave the same way? They may have the same result in the above scenario, but that's because you have no uncommitted transactions.
Connection 1:
BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
-- Start Connection 2
SELECT * FROM T;
COMMIT;
From BOL: "BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent." "Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement." So no "transaction" here...
----------------------------------
Connection 2:
Begin Transaction -- Within Transaction
Update table T ...
WAITFOR DELAY '00:01:00'
COMMIT;
Above case will work like this
Read uncommited: 2nd select in connection 1 will display Update of Connection 2 even though its not commited yet.
Read Commited : 2nd select in connection 1 will not display Update of Connection 2 since its not commited yet. It will wait f
Yes.
So what are you confused about?
I find that the BOL entry that talks about the locks does not address the SELECT query well enough. By using READCOMMITTED or READUNCOMMITTED a select query will only hold the share lock (by default) for the duration of the select query - not the transaction.
With ReadCommitted, the default isolation level, separate select queries within a single transaction (as per connection 1 in your scenario) may not return the same results, i.e. the locks are not held after the first select occurs.
Isolation Level Summary
-----------------------
ReadCommitted : reads committed rows and attains share locks on the rows for the duration of the select only. Locks are released after each select has completed.
Readuncommitted : reads committed and uncommitted rows (new versions of the rows), and attains share locks on the rows for the duration of the select only. Locks are released after each select has completed.
Repeatable Read : reads committed rows and attains share locks on the rows for the duration of the transaction. Locks are released at the end of the transaction. Additional rows can be added and non-selected rows (from the first select) may become within scope (ie may show) dependent upon the changes made.
Serializable : reads committed rows and attains share locks on the rows for the duration of the transaction. Locks are released at the end of the transaction. Additional rows cannot be added and updates will be blocked if the update would cause more rows to appear in the resultset on the second and subsequent select.
From you earlier example
------------------------
Conn1 :
BEGIN TRANSACTION;
SELECT * FROM xTable;
WAITFOR DELAY '00:01:00'
-- Start Connection 2
SELECT * FROM xTable;
COMMIT;
----------------------------------
Conn 2:
Update table xTable ... -- not within a transaction ! (This is an implicit transaction and will have Begin Tran and Commit Tran assumed)
Example (1):
1. Conn1 : Begin Tran
2. Conn1 : select * from xTable (x number of rows returned) - share locks held during the processing of the select but not after
3. Conn2 : Implicit transaction : Begin Tran / Update xTable ... / Commit Tran
4. Conn1 : select * from xTable (different content from above)
5. Conn1 : commit tran
If you wanted to steps 2 and 4 to return the same resultset then a different (higher) level of isolation is required such as repeatable read or serializable.
Assuming that Conn1 has a repeatable read isolation then the example would run differently.
Example (2):
1. Conn1 : Begin Tran
2. Conn1 : select * from xTable (x number of rows returned) - share locks held now until the commit tran (step 5)
3. Conn2 : Implicit transaction : Begin Tran / Update xTable ... / Commit Tran (will have to wait if rows being updated are part of conn1 resultset)
4. Conn1 : select * from xTable (same content from above)
5. Conn1 : commit tran
Fitz
April 19, 2012 at 9:43 am
itskanchanhere (4/19/2012)
The more I read on Isolation levels the more I get confusedConnection 1:
BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
-- Start Connection 2
SELECT * FROM T;
COMMIT;
----------------------------------
Connection 2:
Update table T ... -- not within a transaction
In above senario both Read commited and Rean uncommited Isolations will behave in the same way. 2nd select in Connection 1 will show updates from Connection 2 and hence will be different from Connection 1. Then how are the 2 different??
Since the select statement doesn't hold a lock on the table the update can still modify the data. Connection 1 takes a shared lock to read the table and, since nothing has changed, releases it. Since there's no lock the update takes an exclusive lock, modifies data, and commits and releases the lock. Which means with the second select the data is committed and can be read. If you were using repeatable read or serializable then the lock from the first select would be held and block the update so the second select doesn't return different results.
April 19, 2012 at 9:48 am
cfradenburg (4/19/2012)
itskanchanhere (4/19/2012)
The more I read on Isolation levels the more I get confusedConnection 1:
BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
-- Start Connection 2
SELECT * FROM T;
COMMIT;
----------------------------------
Connection 2:
Update table T ... -- not within a transaction
In above senario both Read commited and Rean uncommited Isolations will behave in the same way. 2nd select in Connection 1 will show updates from Connection 2 and hence will be different from Connection 1. Then how are the 2 different??
Since the select statement doesn't hold a lock on the table the update can still modify the data. Connection 1 takes a shared lock to read the table and, since nothing has changed, releases it. Since there's no lock the update takes an exclusive lock, modifies data, and commits and releases the lock. Which means with the second select the data is committed and can be read. If you were using repeatable read or serializable then the lock from the first select would be held and block the update so the second select doesn't return different results.
Sorry to be perdantic but the repeatable read would allow updates to occur as long as no rows were removed from the result set due to the update or insert. More rows could be returned with repeatable read.
Fitz
April 19, 2012 at 10:15 am
No worries but I'm going to one-up your pedanticness. 🙂 Updates that are inserts will run. However, they're not modifying data that was read, they're creating new data. Updates that are either updates or deletes will have problems (as long as they're modifying read data). That's true regardless of whether or not rows are removed from the results.
--Connection 1 Prep
set transaction isolation level repeatable read
create table dbo.T (
id int identity,
name varchar(30)
)
insert into T (name) values ('Johnny')
--Connection 1 Demo
begin transaction
select * from dbo.T
--Run queries for connection 2 here
rollback
--Connection 2
update dbo.T set name = 'Jim Bob' where id = 1
delete from dbo.T where id = 1
insert into dbo.T (name) values ('Mary Sue')
April 19, 2012 at 10:41 am
Agreed.;-)
Fitz
April 19, 2012 at 11:34 am
You have no idea how tempted I was to say, "as long as the update isn't an update," but I didn't want to do anything that could confuse the explanation.
April 19, 2012 at 3:01 pm
itskanchanhere (4/19/2012)
The more I read on Isolation levels the more I get confused
I really don’t blame you. Transactions and isolation can be a very difficult and confusing, and BOL does not help, especially working with different isolation levels and updates doing inserts.... 🙂
Mark Fitzgerald-331224 (4/19/2012)
Isolation Level Summary
-----------------------
Readuncommitted : Reads committed and uncommitted rows (new versions of the rows), and attains share locks on the rows for the duration of the select only. Locks are released after each select has completed.
This is not correct. Readuncommited does not take any locks. That’s how uncommitted records can read data that is exclusively locked(it disregards any lock locks)
A quick overview of how selects are handled in the different isolation levels:
1)Read uncommitted: No Locks
2)Read commited: Shared Lock, but release as soon as it returns the results.
3)Repeatable read: Shared lock, but keeps it until the end of the transaction
4)Serializable: Same as above, but in addition it also takes key-range locks until the end of the transaction to insure that new data can not be added in the queries ranges. Eg. If your select statement have a BETWEEN 0 and 1000000 clause, it will lock the range even if those values does not yet exist.
5)Read committed snapshot: It won’t issue any locks, and use the version store to take a copy of the most recent committed version of the data as of the beginning of the statement. Therefore, is does not have to wait for any modification transactions to finish.
6)Read committed snapshot: As above, but takes a copy of the most recent committed version of the data as of the beginning of the transaction.
April 19, 2012 at 3:06 pm
EDIT: My bad response omitted to remove any confusion...
Jared
CE - Microsoft
April 19, 2012 at 3:12 pm
Eh... Read this by Paul White. http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx
Jared
CE - Microsoft
April 19, 2012 at 3:35 pm
SQLKnowItAll (4/19/2012)
stevro (4/19/2012)
itskanchanhere (4/19/2012)
The more I read on Isolation levels the more I get confusedI really don’t blame you. Transactions and isolation can be a very difficult and confusing, and BOL does not help, especially working with different isolation levels and updates doing inserts.... 🙂
Mark Fitzgerald-331224 (4/19/2012)
Isolation Level Summary
-----------------------
Readuncommitted : Reads committed and uncommitted rows (new versions of the rows), and attains share locks on the rows for the duration of the select only. Locks are released after each select has completed.
This is not correct. Readuncommited does not take any locks. That’s how uncommitted records can read data that is exclusively locked(it disregards any lock locks)
I think it is assumed that the shared lock is only placed on committed data, as you cannot lock data that is not committed. EDIT: I think...
Sorry my bad, copy paste error
Fitz
April 19, 2012 at 3:54 pm
SQLKnowItAll (4/19/2012)
I think it is assumed that the shared lock is only placed on committed data, as you cannot lock data that is not committed. EDIT: I think...
Sure you can. All updates, deletes and inserts lock uncommitted data. If they didn't, then any select could read that uncommitted data regardless of isolation level, which would be bad. Now it won't be a shared lock, it'll be X because for the data to be uncommitted, it must have been modified and hence it'll be X-locked til the end of the transaction.
Read uncommitted does not take shared locks. That's how it can read uncommitted rows that inserts, updates and deletes have locked. Because it does not take locks, it can't be blocked waiting for a lock to be granted. It has no effect on data modifications which will still take their X locks as required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2012 at 3:59 pm
stevro (4/19/2012)
6)Read committed snapshot: As above, but takes a copy of the most recent committed version of the data as of the beginning of the transaction.
I think 6 needs to read "Snapshot isolation"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply