Unanswered: Inserts resulting in Exclusive Key Lock
I'm relatively new to SQL and I've come across something that doesn't seem quite right. When an insert becomes part of an transaction I notice an exclusive KEY lock in Enterprise Manager. The table in question was using a Clustered index but I changed that, dropped the table and brought it back in but I still get the lock which keeps all others out of the table. Is this the expected behavior or is there something I am missing? Could the size of the tabe affect things? This is a very small table currently. I'm using MSSQL 7 sp3.
Yes, 7 has row locking, not sure about the page size. I am converting and old Btrieve app and trying to keep modifications to the code to a minimum. This is acutally happening with only one user in. Within a tranasction its reading a table, inserting a row, then cycyling again - multiple times. After the first insert its not able to read the table again. Before I went in and checked if the code can be changed I wanted to make sure there isn't something else I could do - aside from changing the isolation level.
Originally posted by Brett Kaiser
Well put the clustered index back...
It's locking the row and/or page..
Did 7 have row? And was it a 2k or 8k page?
I can't remeber any more,
BUT, you need to keep your transactions brief....like lightning breif....