Table Lock on a Simple Update .. Using PrimaryKey as the only value in the WHERE clause // The whole table locks

Question

Table Lock on a Simple Update .. Using PrimaryKey as the only value in the WHERE clause // The whole table locks

sholliday

SSCrazy

Points: 2100
More actions
April 19, 2006 at 5:08 pm

#114429

The short of it.. is that I have a table ... with a PrimaryKey

I'm doing a simple Update statement.... using the PrimaryKey as the only item in the WHERE clause.
The update is on a field...which is indexed (non clustered)

When I check the locking..... it is locking the table. (it has about 8 locks, but the disturbing one is the TAB lock)

My table has 2,000,000 rows.. and its doing a table lock for updating a record in the table.. and killing my performance.

I have isolated the TRAN to be the smallest update it can be.. handling all READs outside of the TRAN

Below is an example I created... to use the Northwind and mimics what I am doing.

Can anyone throw some light onto why the TAB lock is kicking in????

Thanks!

..

Other Info:
Sql Server 2000
Paralleling the example below, my "Order" table would have 2,000,000 rows.. my Order Detail table would have 200 rows or so.

I've dumbed this down to be able to show it via the Northwind database....

This is assuming a few things:
OrderID = Primary Key .
and that there is an Index on OrderDate
(which on my version of the Northwind database .. is the setup )

I have 2 scripts. One of them does a little logic to figure out an OrderID..... then attempts to update the OrderDate on that singular OrderID
    (I understand that using the productid to get the OrderID is a little fudgy.... this is an example and translation from what I am doing in my code)

I have purposely left the "COMMIT TRAN" off.. so I can do a little testing... and see what its locking.

I then run the second script to check the locks.

I am seeing a TAB (table) lock on the Order table.

I can't figure out why that is happening.. or why it should happen.

(Fyi, after I view the results of script TWO, I go back to scriptONE and run ROLLBACK TRAN ... )

As you can see, I've tried to use UPDLOCK and ROWLOCK. I know UPDLOCK isn't quite right, but I tried it anyway.
I've been looking at this for 2 days now... and read alot about the rowlocking schemes. So one-word responses probably aren't what I'm looking for.
But I greatly appreciate any help.

Here are the Scripts:

--------------- script ONE

declare @ProductID int

select @ProductID = ProductID from dbo.[Order Details] -- just grab one, it doesn't matter that much which one
    -- feel free to replace the query with a legit value if you'd like ... ex -- Select @ProductID = 10248

print @ProductID

-- this will be a order we find using the productid
declare @OrderID int

SELECT
@OrderID = OrderID--, -- I'm just getting an OrderID by using the ProductID ... I know there may be many Orders... but I only want one
FROM
dbo.[Order Details]
WHERE
ProductID = @ProductID
-- in my code ..... this would bring back 1 exact record .. for the given ProductID

BEGIN TRAN -- BEGIN it, but **purposely leave it hanging out to dry**

  UPDATE dbo.[Orders]
   --WITH (UPDLOCK)
   --WITH (ROWLOCK)
  SET
   OrderDate = GETDATE()
  WHERE OrderID = @OrderID -- this will match exactly one record .. here and in my code

-- COMMIT TRAN -- commented out ... to leave the locks in place .....

if 1 = 2
begin
   ROLLBACK TRAN -- after checking the locks with the other query... run this standalone to clear it out
end

---------------- end script 1

----------------- script 2

-- you have to change the value to your ID of your Northwind database ... this is some code I found on the internet, but sp_lock (and the code I found for sp_lock2) show similar
declare @DataBaseID varchar (5)
set @DataBaseID = 8
/* put here your number (get it from sp_helpdb)*/
select
substring (v.name, 1, 4) As Type,
object_name(rsc_objid)as ObJName,
convert (smallint,req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
object_name(rsc_objid)as ObJName,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
-- master.dbo.sysdatabases DB
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_objid <> 0
and rsc_dbid = @DataBaseID
order by v.name--spid

-- end script 2

-- I posted this on the sql.programming microsoft newsgroup also.

Viewing 2 posts - 1 through 1 (of 1 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What type is the TAB lock? S, X, IX, Sch-S?

An IX lock on table level is fairly normal when doing modifications. It's not a full exclusive lock, it's there in case SQL has to escalate the page/row locks to table. Usually if there's an X row lock there will be a IX page/extent lock and if there's an X page/extent lock then there will be a IX table lock

From Books Online:

As the Microsoft SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects containing the lower-level objects:

When locking rows or index key ranges, the Database Engine places an intent lock on the pages containing the rows or keys.
When locking pages, the Database Engine places an intent lock on the higher level objects containing the pages. In addition to placing an intent object (OBJECT) lock on the table, intent heap or B-tree (HOBT) locks are requested on:
- The nonclustered index if the pages are nonclustered index pages.
- The clustered index if the pages are clustered index pages. This includes data pages in tables that have a clustered index.
- The heap of data pages if the pages are data pages in a table that does not have a clustered index.

An UPDATE statement acquires these locks:

Exclusive (X) locks on the updated data rows.
Intent exclusive (IX) locks on the clustered index pages containing those rows.
An IX lock on the clustered index and another on the table.

An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions.

--

It would be helpful if you could post the structure of your table, including indexes and the query and what locks you see.

Check the execution plan, make sure that the optimiser isn't suggesting a table scan. Check and make sure your indexes aren't too fragmented.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass