Relation b/w SQL Server transaction isolation levels and locks

I had a very good discussion with a colleague of mine at work on the impact of SQL statements under the scope of a transaction. We were trying to optimize a stored procedure for the minimum execution time. While going through the SP at one point, We found a SELECT statement followed by an UPDATE statement inside a transaction, something like this:

BEGIN TRANSACTION

SELECT* from dbo.authors WHERE au_fname LIKE 'Johnson'

UPDATE authors SET au_fname = 'Johnson1' WHERE au_id = '172-32-3176'

COMMIT TRANSACTION

My colleague was of the view that, one should always keep the transaction as small as possible and SELECT statement should not unnecessarily be made part of the transaction. This helps ensure the locks will be held for a minimum period of time and maximum availability of the table or rows to other transactions. I couldn't disagree with him on this.

He added that, if the SELECT statement is not contributing in the overall outcome of the transaction, then there is no point in having it inside the transaction. In light of this argument, we can easily take the SELECT statement out of transaction I have mentioned above. However, part of his argument spurred me to do a small research on SQL Server transaction isolation level and locking where he said that all the locks will be held and table/rows will be unavailable for other transactions right from the start of the transaction.

According to my understanding, transaction isolation level defines the behavior of locks. SELECT statements acquire SHARED LOCK while UPDATE, DELETE and INSERT statements acquire EXCLUSIVE. While executing SELECT under READ COMMITTED isolation level which is the default for SQL Server, locks will immediately be released as soon as the execution of SELECT statement is finished. SQL Server will not wait for the transaction to be over and will allow other transaction to modify the table or rows. However, in case of REPEATABLE READ isolation level, every other process will have to wait for the locks regardless of SELECT statement has been executed or not. The below definition of REPEATABLE READ from MSDN helps use understand this point.

"REPEATABLE READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data (but can be add) that has been read by the current transaction until the current transaction completes"

BEGIN TRANSACITON plays no role in defining the scope of the locks which are not applied until SQL Server reaches to a particular statement (SELECT, INSERET, UPDATE, DELETE). Based upon the definitions of different isolation levels and locks, we can easily understand the association b/w them. In the light of this analysis, for the above transaction, I reached to an understanding that, it will make no difference whether we keep the SELECT statement inside or outside the transaction. It might take longer to come back because of the SELECT statement but, it will not prevent other transaction from acquiring locks until it reaches to UPDATE statement. I supported my argument by running the following test:

Process 1:

Use Pubs

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

select * from authors WHERE au_fname like 'Johnson' /* Statement 1 */

Insert Into authors values('172-32-4188', 'ben', 'Johnson', '406 496-7229', 'address', 'city', 'CA', 94445, 1) /* Statement 2 */

select * from authors WHERE au_fname like 'Johnson'        /* Statement 3 */

Commit Transaction

Process 2:

Use Pubs

Insert Into authors values('172-32-4188', 'ben', 'Johnson', '406 496-7229', 'address', 'city', 'CA', 94445, 1) //will get executed

UPDATE authors set au_fname = 'Johnson1' where au_id = '172-32-3176' /* will have to wait until transaction in Process 1 is finished.

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
•