Sunday, July 12, 2015

green to using nolock

I was surprised when this worked all by itself...

BEGIN TRANSACTION setup
   INSERT INTO Note (WhateverId, Copy)
   VALUES (@WhateverId, 'hello world')
   DECLARE @NoteId int
   SELECT @NoteId=NoteId FROM Note
   WHERE WhateverId = @WhateverId
   INSERT INTO Affidavit (WhateverId, NoteId, Copy)
   VALUES (@WhateverId, @NoteId, 'hello lawsuit')
COMMIT TRANSACTION setup

 
 

...without a nolock like so:

BEGIN TRANSACTION setup
   INSERT INTO Note (WhateverId, Copy)
   VALUES (@WhateverId, 'hello world')
   DECLARE @NoteId int
   SELECT @NoteId=NoteId FROM Note with(nolock)
   WHERE WhateverId = @WhateverId
   INSERT INTO Affidavit (WhateverId, NoteId, Copy)
   VALUES (@WhateverId, @NoteId, 'hello lawsuit')
COMMIT TRANSACTION setup

 
 

I guess I am green to using nolock. If I don't need it here, when do I need it? This suggests that this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 
 

...will globally allow for not needing locking, but when I asked myself "How may I tell what the transaction isolation level is?" I then found this which has...

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID

 
 

...which handed me back ReadCommitted. I guess my local environment isn't wacky. So... when do I need to lock?

Addendum 7/13/2015: I have learned that nolock in T-SQL does NOT apply to the transaction at hand (allowing you to read uncommitted rows), but instead it applies to the other transactions out there in space (allowing you to read uncommitted rows) at the same time from other connections.

No comments:

Post a Comment