asked
asp had sql error, now transaction is locked, how to locate/terminate transaction?
I ran my asp page and got this error: Execution Error: The statement has been terminated. The INSERT statement conflicted with the CHECK constraint "SSMA_CC$tblContracts$ModDate$validation_rule". The conflict occurred in database "GCTest05", table "dbo.tblContracts", column 'ModDate'. Now in SQL Server 2008 Express Mgmt Studio, I try to delete the offending Check constraint, but get "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)." Then get referred to this website
http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1222 says Explanation Another transaction held a lock on a required resource longer than this query could wait for it. User Action Perform the following tasks to alleviate the problem: Locate the transaction that is holding the lock on the required resource, if possible. Use sys.dm_os_waiting_tasks and sys.dm_tran_locks dynamic management views. If the transaction is still holding the lock, terminate that transaction if appropriate. Execute the query again. ...So how do I locate and then terminate the transaction? Do I need a ROLLBACK statement in each asp page if I get an error?
I would run sp_who2 and see what sessions are currently blocking. If you have an open transaction that isn't being committed but is blocking you can obtain the SPID and this issue the command KILL SPID where SPID is the SPID number. Before killing the SPID you can run DBCC INPUTBUFFER (SPID NUMBER) to see what the session was doing. That way you can troubleshoot the root cause. I hope this helps.
Your asp is not the problem, the transaction that was in place wasnt committed. Thats why you got the error. If you are trying to drop the constraint from SSMS then you have to make sure nothing else is referencing it. If it is then that will cause a lock and the drop will not succeed. It is often best to carry out the drop with TSQL rather than the SSMS GUI to avoid this issue.
To answer your second question: > Do I need a ROLLBACK statement in each asp page if I get an error? Basically, yes you would, if your connection is being kept open. But the fact that your connection is being held open is probably a bigger problem. The simplest way to manage a transaction over a bunch of SQL statements from within C# or .NET is [to use TransactionScope][1] - but if you're using ASP Classic (VBScript) then it's pretty much up to you. [1]:
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx
