Post reply

Strange blocking issue

  • December 11, 2012 at 3:50 am

    #266085

    Hi Experts,

    I came across a blocking issue in which the below two process are involved the second statement is blocked by first,but the strange part is they don't have anything in common

    insert bulk [dbo].[table1]([MeterReadID] uniqueidentifier,[MeterID] uniqueidentifier,[AssetID] uniqueidentifier,[EntryDate] datetime,[ReadDate] datetime,[Count] bigint,[UserName] nvarchar(50),[MeterSourceID] uniqueidentifier,[MeterExtendedSourceID] uniqueidentifier,[IsRollOver] bit,[StartRead] bigint,[Comment] nvarchar(50),[IsInvalid] bit,[IsCredit] bit,[Credits] bigint,[IsEstimate] bit,[IsSuspect] bit,[IsSuspectHigh] bit,[IsVirtual] bit,[IsRefill] bit,[RefillCount] bigint,[CreatedDate] datetime,[CreatedBy] uniqueidentifier,[ModifiedDate] datetime,[ModifiedBy] uniqueidentifier,[DWIsDeleted] bit,[DWStartDate] datetime,[DWEndDate] datetime,[IsInstall] bit,[IsFinal] bit)

    DECLARE @DuplicateRows bigint, @RowsUpdated bigint ; DELETE c FROM dbo.[table2] AS c INNER JOIN dbo.[table2_Deletes] AS a ON c.[MeterReadID] = a.[_MeterReadID] AND c.DWEndDate = a.[ModifiedDate] ; SET @DuplicateRows = @@ROWCOUNT ; UPDATE c SET c.DWEndDate = ISNULL(a.[ModifiedDate], GETDATE()) FROM dbo.[table2] AS c INNER JOIN dbo.[table2_Deletes] AS a ON c.[MeterReadID] = a.[_MeterReadID] WHERE c.DWEndDate IS NULL AND a.IsPruned = 0 ; SET @RowsUpdated = @@ROWCOUNT ; DROP TABLE dbo.[table2_Deletes] ; SELECT @DuplicateRows AS DuplicateRows, @RowsUpdated as RowsUpdated ;

    The delete statement is blocked by bulk insert. Can anyone please point out why this happened?

  • December 11, 2012 at 4:52 am

    #1567378

    Any chance of referential integrity between these 2 tables?

  • December 11, 2012 at 4:55 am

    #1567379

    The delete must have been waiting for the bulk insert to release a lock on some resource it had held open.

    If you have historic reporting, try looking at what locks where held for the bulk insert, or debug where both of the statements could execute at the same time for transactional concurency and things not committing transactions where they should etc.

  • December 11, 2012 at 5:17 am

    #1567387

    Hi,

    Check sp_whoisactive procedure after running the above statements. It will tell you which session id is blocing the table.

    OR

    U can check the information sys.dm_tran_locks to see if there are any shared and exclusive locks on it.

    For more information goto

    Adam machanic blog.

    http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspx

  • December 11, 2012 at 5:19 am

    #1567388

    Thanks SQLACT & Anthony for the reply.

    Will check those and update you guys

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply