We help IT Professionals succeed at work.

Exclusive lock on table with 'Insert'

ykchakri
ykchakri asked
on
Medium Priority
1,136 Views
Last Modified: 2008-03-10
Hi Experts,

Is it normal for an 'Insert' statement on a table, to put an Exclusive (X or IX) lock on the table.
Here is what I tried:

Begin transaction
Insert into northwind..shippers values ('xyz', '(123) 456-7890')

When I check now in the 'Current Activity' tab in EM, there are the following locks on 'Shippers' table:
Exclusive lock on KEY (Row level lock)
Intent Exclusive lock on PAG (Page lock)
and Intent Exclusive lock on TAB (Table lock)

Because of this, I am not even able to do a select on this table, till I commit the previous Insert.

A key and page locks are understandable in the above situation, but why table lock and how can we avoid this ?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
My Northwind installation has a Primary Key on field

ShipperID of Shippers table. Check if yours has one

execute sp_MShelpindex Shippers

if not run

Alter table Shippers ADD CONSTRAINT PK_Shippers PRIMARY KEY
( ShipperID )


and try running this insert again.

Author

Commented:
Yes,

My Shippers table is also having a primary key on 'ShipperID' field.
Any other suggestions please?
CERTIFIED EXPERT

Commented:
Here is how IX lock works if a PK is defined on the table.
An individual row insert should not cause an X IX lock on a table. An X / IX lock will be acquired on the row. However if transactions structured this way:
Many individual statements executing
Select on many rows in table
Many individual statements executing
Insert single row
and more than one user execute this logic concurrently on the same table, a lock escalation might occur causing an IX lock to be acquired by table on SQL Server. To avoid it put entire transaction logic, including select on many rows - insert in a stored procedure. That way locks will be bound to a transaction and will not cause cross transaction lock escalation.

On the other hand when inserted large chunk of data consisting of many rows such as bcp, BULK INSERT, and / or large insert update, then IX table lock is the optimal way for the transaction. However this also can be conquered on SQL Server 2000 by using partitioned updateable views - look for help in the On - Line documentation.

Author

Commented:
Thank you Miron,

But, in my previous example with 'Shippers' table, none of these conditions hold true, right? There is a PK on this table, I am running only 1 insert statement on this table, And I am the only one using this table etc. Also, from your previous comment on 09/23, can I assume that you were not getting these locks on this table ? If yes, then can there be any configurational differences between your env. and mine ? I am running this test on a SQL 2000 machine with default config.

Thank you once again, for your time on this question.
CERTIFIED EXPERT
Commented:
I have an identical setup, but no table locks, as you guessed. I am wandering what technique used to collect data on the locks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.