Locking behavior causing concurrency issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Locking behavior causing concurrency issue

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2007-06-07 : 14:40:50

Hi,

We have a batch job that runs for about 45 min and reference couple of tables in queries but on one of the table DD (dummy name) we want to take a shared lock so that other sessions can read the table but no session should be able to write to table, so at the beginning of transaction/batch job we take a SHARED level table lock on this table. Now the locking behavior is strange, If someone want to read the table (IS on table DD) they are working fine but as soon as one of the DML wants to modify the table and get blocked to get IX lock on DD other sessions who just wants to read the table and need to get the IS lock gets blocked so system is kind of hung after that. I tried the same scenario on SQL Server 2005 and it works fine so this looks like kind of bug in SQL Server 2000 unless Microsoft consider this as “AS Designed” locking strategy. We can the following queries to reproduce the issue:

From QA window1 run the following code:
create table dd (dd int primary key, dd1 int,dd2 int)

set nocount on
declare @i int
begin
set @i=1
while (@i < 10000)
begin
insert into dd values(@i,@i+10000,@i+100000)
set @i=@i+1
end
end

Now we have a table with about 26 pages and 10k rows.

Now we run the following code from same window:
Begin Tran
select 1 from dd with(tablock,holdlock)
Don’t commit at this point

And we will see the following using sp_lock:
57 7 850818093 0 TAB S GRANT

Now on Window2 in QA run the following code:
begin tran
insert into dd values(10000,10001,100001)

And we will see that session in window2 is waiting for IX lock:
56 7 850818093 0 TAB IX WAIT
57 7 850818093 0 TAB S GRANT

Now we run the following code from Windows3, we are just reading the table and it still gets blocked:
select * from dd where dd=99

56 7 850818093 0 TAB IX WAIT
57 7 850818093 0 TAB S GRANT
66 7 850818093 0 TAB IS WAIT

Since this is fixed in SQL Server 2005 we are wondering if this is known issue and there is any hotfix available or this is considered by Microsoft as “as designed?
What is the other alternative to block writers on this table and allow readers when this batch job is running?

Thanks
--Harvinder

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-08 : 01:18:53

Tried set proper isolation level for reader process?