NOLOCK being blocked
in [Programming SQL]
|
Prev: Display of DATE format in Management Studio
Next: EXECUTE permission denied on object 'sp_send_dbmail', database
From: CLM on 8 Oct 2009 14:52 Okay, this is weird. (SS 2005 SP2 x64) I've got a simple SELECT statement with a NOLOCK spid (139) being blocked by a replication spid (102). The replication spid has an IX HBT (Heap/BTree) lock is all. I thought if you did NOLOCK you couldn't get blocked unless someone was actually doing DDL/Schema changes? Is that incorrect? In other words, even with a bulk operation, shouldn't I be able to do a dirty read SELECT on the table? --139 blocked by 102 per sp_who2 --Here's 139: SELECT COUNT(*) "Expr1006" FROM "NDBCo"."dbo"."tbl_SectionDa" "Tbl1005" WITH (NOLOCK) --Here's 102: insert bulk "dbo"."tbl_SectionDa"([EVID] int,[VAXEVID] bigint,[System] char(3) collate SQL_Latin1_General_CP1_CI_AS,[XNum] int,[XName] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[PriceLevel] int,[ExitPort1] varchar(20) collate SQL_Latin1_General_CP1_CI_AS,[ExitPort2] varchar(20) collate SQL_Latin1_General_CP1_CI_AS,[RowsPerXNum] int,[SeatsPerRow] int,[StartRow] varchar(10) collate SQL_Latin1_General_CP1_CI_AS,[StartSeat] varchar(10) collate SQL_Latin1_General_CP1_CI_AS,[RowSequence] varchar(500) collate SQL_Latin1_General_CP1_CI_AS,[SeatSequence] varchar(500) collate SQL_Latin1_General_CP1_CI_AS,[SellClass] int)with( no_triggers, TABLOCK,KEEP_NULLS,ROWS_PER_BATCH=2147473647)
From: TheSQLGuru on 8 Oct 2009 15:00 NOLOCKs can be blocked. Search the web. Here is one result: http://sqlblog.com/blogs/linchi_shea/archive/2009/08/03/performance-impact-can-select-nolock-block-inserts.aspx -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "CLM" <CLM(a)discussions.microsoft.com> wrote in message news:AB61A675-3A69-49C7-8F56-8DBCFBFD3A81(a)microsoft.com... > Okay, this is weird. (SS 2005 SP2 x64) I've got a simple SELECT statement > with a NOLOCK spid (139) being blocked by a replication spid (102). The > replication spid has an IX HBT (Heap/BTree) lock is all. I thought if you > did NOLOCK you couldn't get blocked unless someone was actually doing > DDL/Schema changes? Is that incorrect? In other words, even with a bulk > operation, shouldn't I be able to do a dirty read SELECT on the table? > > > --139 blocked by 102 per sp_who2 > > --Here's 139: > > SELECT COUNT(*) "Expr1006" FROM "NDBCo"."dbo"."tbl_SectionDa" "Tbl1005" > WITH > (NOLOCK) > > --Here's 102: > > insert bulk "dbo"."tbl_SectionDa"([EVID] int,[VAXEVID] bigint,[System] > char(3) collate SQL_Latin1_General_CP1_CI_AS,[XNum] int,[XName] > varchar(50) > collate SQL_Latin1_General_CP1_CI_AS,[PriceLevel] int,[ExitPort1] > varchar(20) > collate SQL_Latin1_General_CP1_CI_AS,[ExitPort2] varchar(20) collate > SQL_Latin1_General_CP1_CI_AS,[RowsPerXNum] int,[SeatsPerRow] > int,[StartRow] > varchar(10) collate SQL_Latin1_General_CP1_CI_AS,[StartSeat] varchar(10) > collate SQL_Latin1_General_CP1_CI_AS,[RowSequence] varchar(500) collate > SQL_Latin1_General_CP1_CI_AS,[SeatSequence] varchar(500) collate > SQL_Latin1_General_CP1_CI_AS,[SellClass] int)with( no_triggers, > TABLOCK,KEEP_NULLS,ROWS_PER_BATCH=2147473647) > >
From: Linchi Shea on 8 Oct 2009 16:52 The statement that you show in spid 102 may not itself the root of the blocking. You may want to take look at the locks are outstanding. Most likely, it's blocked on a Sch_M lock, and most likely it's TRUNCATE TABLE on spid 102 that's exeucted prior to insert bulk but in the same transaction. Linchi "CLM" wrote: > Okay, this is weird. (SS 2005 SP2 x64) I've got a simple SELECT statement > with a NOLOCK spid (139) being blocked by a replication spid (102). The > replication spid has an IX HBT (Heap/BTree) lock is all. I thought if you > did NOLOCK you couldn't get blocked unless someone was actually doing > DDL/Schema changes? Is that incorrect? In other words, even with a bulk > operation, shouldn't I be able to do a dirty read SELECT on the table? > > > --139 blocked by 102 per sp_who2 > > --Here's 139: > > SELECT COUNT(*) "Expr1006" FROM "NDBCo"."dbo"."tbl_SectionDa" "Tbl1005" WITH > (NOLOCK) > > --Here's 102: > > insert bulk "dbo"."tbl_SectionDa"([EVID] int,[VAXEVID] bigint,[System] > char(3) collate SQL_Latin1_General_CP1_CI_AS,[XNum] int,[XName] varchar(50) > collate SQL_Latin1_General_CP1_CI_AS,[PriceLevel] int,[ExitPort1] varchar(20) > collate SQL_Latin1_General_CP1_CI_AS,[ExitPort2] varchar(20) collate > SQL_Latin1_General_CP1_CI_AS,[RowsPerXNum] int,[SeatsPerRow] int,[StartRow] > varchar(10) collate SQL_Latin1_General_CP1_CI_AS,[StartSeat] varchar(10) > collate SQL_Latin1_General_CP1_CI_AS,[RowSequence] varchar(500) collate > SQL_Latin1_General_CP1_CI_AS,[SeatSequence] varchar(500) collate > SQL_Latin1_General_CP1_CI_AS,[SellClass] int)with( no_triggers, > TABLOCK,KEEP_NULLS,ROWS_PER_BATCH=2147473647) > >
From: CLM on 8 Oct 2009 18:00
Makes sense! "Linchi Shea" wrote: > The statement that you show in spid 102 may not itself the root of the > blocking. You may want to take look at the locks are outstanding. Most > likely, it's blocked on a Sch_M lock, and most likely it's TRUNCATE TABLE on > spid 102 that's exeucted prior to insert bulk but in the same transaction. > > Linchi > > "CLM" wrote: > > > Okay, this is weird. (SS 2005 SP2 x64) I've got a simple SELECT statement > > with a NOLOCK spid (139) being blocked by a replication spid (102). The > > replication spid has an IX HBT (Heap/BTree) lock is all. I thought if you > > did NOLOCK you couldn't get blocked unless someone was actually doing > > DDL/Schema changes? Is that incorrect? In other words, even with a bulk > > operation, shouldn't I be able to do a dirty read SELECT on the table? > > > > > > --139 blocked by 102 per sp_who2 > > > > --Here's 139: > > > > SELECT COUNT(*) "Expr1006" FROM "NDBCo"."dbo"."tbl_SectionDa" "Tbl1005" WITH > > (NOLOCK) > > > > --Here's 102: > > > > insert bulk "dbo"."tbl_SectionDa"([EVID] int,[VAXEVID] bigint,[System] > > char(3) collate SQL_Latin1_General_CP1_CI_AS,[XNum] int,[XName] varchar(50) > > collate SQL_Latin1_General_CP1_CI_AS,[PriceLevel] int,[ExitPort1] varchar(20) > > collate SQL_Latin1_General_CP1_CI_AS,[ExitPort2] varchar(20) collate > > SQL_Latin1_General_CP1_CI_AS,[RowsPerXNum] int,[SeatsPerRow] int,[StartRow] > > varchar(10) collate SQL_Latin1_General_CP1_CI_AS,[StartSeat] varchar(10) > > collate SQL_Latin1_General_CP1_CI_AS,[RowSequence] varchar(500) collate > > SQL_Latin1_General_CP1_CI_AS,[SeatSequence] varchar(500) collate > > SQL_Latin1_General_CP1_CI_AS,[SellClass] int)with( no_triggers, > > TABLOCK,KEEP_NULLS,ROWS_PER_BATCH=2147473647) > > > > |