Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

About Locking in SQL2000

Status
Not open for further replies.

Sepahram

MIS
Apr 30, 2001
6
CA
Hey all,
I have a table and two following transactions:

Table11 ( DD int , CC int ) -- with no PK or index ...

BEGIN TRAN 1
INSERT INTO Table11 VALUES ( 6 , 6 )

-- ROLLBACK TRAN 1
-- COMMIT TRAN 1

BEGIN TRAN 1
SELECT * FROM Table11 WHERE DD = 1
-- ROLLBACK TRAN 1
-- COMMIT TRAN 1

I execute the first and reveiw the lock info as below:

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
76 5 0 0 DB S GRANT
76 5 379148396 0 RID 1:159:4 X GRANT
76 5 0 0 PAG 1:159 IX GRANT
76 1 85575343 0 TAB IS GRANT
76 5 379148396 0 TAB IX GRANT

I have no index on the table. When I execute the second tran before I commit or rollback the first one , it gets blocked. Why?

By the way it doesn't happen when I create a PK , unique constraint or index on DD
Amir,
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top