Friday, September 3, 2010

SQL NOLOCK Example

A co-worker just gave this to me to illustrate "nolock" and its pretty cool.

Purpose: If you are doing SELECTs and data accuracy is not extremely important, use "nolock" to avoid conflicts with other users.

If you have long running processes that do a lot of SELECTs, this can dramatically improve performance.




   1:  create table testdb.dbo.NoLockTest
   2:  (
   3:      ID int identity(1,1),
   4:      Product     varchar(20),
   5:      SalesDate   datetime,
   6:      SalesPrice  int
   7:  )
   8:   
   9:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('PoolTable', GETDATE(), 200)
  10:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('MyTable', GETDATE(), 500)
  11:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('GeoffTable', GETDATE(), 400)
  12:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('LarryTable', GETDATE(), 250)
  13:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('StephenTable', GETDATE(), 360)
  14:   
  15:  select * from testdb.dbo.NoLockTest
  16:   
  17:  -- Query 1
  18:  BEGIN TRANSACTION 
  19:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) 
  20:  VALUES ('PoolTable', GETDATE(), 1000)
  21:   
  22:  -- Query 2 will continue to run until ROLLBACK TRANSACTION OR COMMITE does not apply to any INSERT/UPDATE/DELETE stmt.
  23:  -- only way to stop the query is to use "Cancel Executing Query" from SSMS Menu.
  24:  select count(*) from testdb.dbo.NoLockTest 
  25:   
  26:  -- Query 3 will allow  you to pull data even though there is a lock on the table due to INSERT/UPDATE/DELETE stmt running
  27:  -- in this case we have from Query 1 
  28:  select count(*) from testdb.dbo.NoLockTest with(nolock)
  29:   
  30:  -- Query 4 -- execute below statement.
  31:  ROLLBACK TRANSACTION
  32:   
  33:  -- Query 5
  34:  select count(*) from testdb.dbo.NoLockTest