SQL Server Performance Forum – Threads Archive

IX lock on tables SQL Server 2000

Hi, Anybody experenced the following problems in SQL Server 2000 and how to solve it? 1. create a table test_lock with 2 columns id int and value varchar(50)
2. populate couple thousand rows
3. open query analyizer. start a transaction but don’t commit
begin tran t1
update test_lock set value = ‘qq’ where id = 2 4. open another window in query analyizer. Run sp_lock, we can find IX lock has been put on this table and in page level. 5. any select/update operation from other transactions againest this table will be blocked. Does anybody have solution to this problem? Thanks, Eric

---

Since I’m not a developer, that is what I think. If you begin a transaction, SQL need commit or rollback to know what to do.
I suppose that is why SQL lock. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

well, that’s true. but the point is this IX lock prevents somebody else from reading other entries. for example, while I am updating the entries with id = 1, somebody else try to read the entries with id = 20 and they are blocked. it don’t make sense.

---

Could id 1 and 20 belong to same page? (your post mention page level)
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

no. they are not in the same page. I tried a big table with contents 100000 entries. the whole table is IX locked by update a single row. Thanks, Eric

---

Yes, I assume this is the natural behaviour of Locking. Intent locks are used primarily to ensure that a user cannot take out locks on a table or pages in the table that would conflict with another user’s row locks.
For example, in your case if another user wants to take an exclusive lock on the table and if there is no IX lock on it, your row lock will be ignored, thus losing the data consistency. But, Intent locks only block regular locks (S or X) and should not block other intent locks (IS or IX). Check if your select operation (in step 5) is such that it is trying a full table scan. Also check your Transaction isolation level.

---

Also the indexes you have on the table and their usage by subsequent selects/updates will play a factor in whether the statement is allow through or blocked Cheers
Twan

---

]]>