当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2005 - How do I find out what type of locking a table has?

问题描述:

I'm getting alot of deadlocks on a table.

It is SQL Server 2005

the sp that is getting the error does the following

Delete From

EmployeeAccrualAdj

Where

EmployeeID = @iEmployeeID and

SchemeCode = @sSchemeCode and

AdjTypeCode = @sAdjTypeCode and

EffectiveDate >= @dtDateFrom

I'm trying to work out if the database is doing row, page or table locking when deleting?

Also, I am thinking of re-writing the sp to

declare @ToDelete table (id int IDENTITY(1,1),

AccrualAdjID int NOT NULL)

insert into @ToDelete

SELECT

AccrualAdjID

FROM

EmployeeAccrualAdj

Where

EmployeeID = @iEmployeeID and

SchemeCode = @sSchemeCode and

AdjTypeCode = @sAdjTypeCode and

EffectiveDate >= @dtDateFrom

DECLARE @iCount int, @iMax int

SELECT

@iCount = 1, @iMax = MAX(id)

FROM

@ToDelete

declare @iAccrualAdjID int

WHILE @iCount <= @iMax

BEGIN

select @iAccrualAdjID = AccrualAdjID FROM @ToDelete WHERE id = @iCount

DELETE FROM EmployeeAccrualAdj

WHERE @iAccrualAdjID = AccrualAdjID

SET @iCOunt = @iCount + 1

END

Effectivly looping through the rows to be deleted and deleting them one at a time.

I'm thinking this will not make any difference if there is row locking , but will if there is page or table locking

What do you all reckon??

网友答案:

The answer depends on the lock escalation rules. The cursor-based approach you are wondering about will be amazingly slow if you have very many rows. I'd look into what other queries are hitting that table during the DELETE operation and see whether you can block them for the duration of the delete with a preemptive lock acquisition.

This article gives an example of how to acquire the needed locks up front with an UPDLOCK locking hint.

分享给朋友:
您可能感兴趣的文章:
随机阅读: