nolock experiment scripts

• Gabriel P

  SSCarpal Tunnel

  Points: 4289

  More actions

  June 2, 2015 at 3:41 pm

  #317892

  Does anyone have any scripts which show how nolock can produce duplicate records in a select statement? I remember Itzik Ben Gan demonstrating with a pair of scripts at SQL Pass a long time ago. All I remember is that one script involved an infinite while statement, and the other involved doing selects in which u had to run the script manually

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 2, 2015 at 6:03 pm

  #1802476

  Yes, I do:

  Setup some test data first:

  use tempdb;

  go

  -- create some test data, only 10 rows is fine

  if object_id('test') is not null

  drop table test;

  go

  create table [dbo].[test](

  [id] [char](10) not null,

  [dated] [date] null,

  [value] [money] null,

  constraint [pk_test_46137] primary key clustered

  (

  [id] asc

  )with (fillfactor = 100)

  ) ;

  go

  insert test(id, dated, value)

  select top 10 cast(row_number() over(order by @@spid) as char(10)), dateadd(day, row_number() over(order by @@spid), '1 jan 2010'), cast(abs(checksum(newid())) as money)

  from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(a)

  go

  Then open two new sessions:

  Session 1

  use tempdb;

  go

  -- use a cte to slow down our select so we have time to change some data

  with delay as

  (

  select cast(1 as bigint) as c

  union all

  select cast(c+1 as bigint)

  from delay

  where c<300000 -- increase this number to give yourself more time to run the second query

  )

  select *

  into test2

  from test with(nolock)

  cross apply (

  select count(*)

  from delay

  where c <> value

  ) x(x)

  order by id

  option(maxrecursion 0, maxdop 1);

  -- display the results - you should have two entries for 2nd Jan 2010

  select *

  from test2

  order by dated;

  -- clean up this session

  drop table test2;

  Session 2

  use tempdb;

  go

  update test

  set id='9999999999'

  where id = '1';

  Run the code in Session 1 (Read with nolock)

  Wait a second or two, the run the code in the Session 2 (update)

  Switch back to Session 1 and you should see a duplicate for 2nd Jan 2010.

  It is slightly contrived, but demonstrates the problem.

  EDIT: Wait, what? I just tried this without NOLOCK and it still created duplicates! More research needed!!! :w00t:

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]