Thread: Problem with ghost locking...
09-28-04, 13:38 #1Registered User
- Join Date
- Jun 2004
- Tel Aviv - Israel
Unanswered: Problem with ghost locking...
I just now had the strangest error, for some reason a table in my DB was locked by two processes, there is no use with BEGIN TRANSACTION in the queries that execute on the DB and while it suddnely happaned no heavy process was running... I was just browsing the table with the enterprise manager.
After viewing the processes with sp_lock I found the two processes that caused the locking and killed them, this fixed the problem, but I would like to know what caused it...
When viewing the process info with sp_who the status was "sleeping" and in sp_lock in one process the type was "key" and the status was "wait" (mode "s").
Any ideas ?
I'm using SQL-Server y2k + sp3.
09-28-04, 15:31 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
Mode S means that the 2 processes where doing SELECT with default TIL (READ UNCOMMITTED) while you were trying to UPDATE the table (maybe accidentally you hit a key while browsing the table in EM?) UPDATE is not allowed on a table that has shared lock placed on it."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."