I dont know a way to commit a transaction of another process.
But I have one idea which could work:
It is based on bound transactions. There is a way in Sql server for two or mor connections to share the same transaction. When you bind a connection to the one you want to commit then you share the same transaction an are able to commit it.
But there is one difficulty: you have to get a bind token which must be issued by the connection you want to bind to.
The process you want to bind has to execute sp_getbindtoken and store the value somewhere so that you can it use later when you execute sp_bindsession.
You could extend a stored procedure with code that stores the result of sp_getbindtoken together with the current process id (=@@spid) in a table where you can fetch it then.
Then you bind your session to the sleeping one, commit the transaction and kill then the sleeping process.
You have to take care that you dont get any unexpecte behaviour in your application, especially if the transaction is part of a distributed transaction.
Also make sure that all the locks of the process are grantet and not a single one has the status 'WAIT'.
When you consider to do it like I described here you have to think about a proper cleanup for the bind token and process ID information table.
Anyway It would be best if you looked at the cause of the problem (debugging) and take a close look on the source code of the application. I think you should try to determine where the application goes to sleep and what does it miss to execute, maybe it is just delayed for some time and then continues to work as expected. Probably there is a reason for the latency of the sleeping process.
regards
FZ
-----Original Message-----
From: Schlieper Paul
[mailto:mssqldba-ezmlmshield-x52043521.[Email address protected]
Sent: Freitag, 28. Mai 2004 17:54
To: LazyDBA.com Discussion
Subject: RE: SQL Server not releasing locks
OK, one more question: assuming the cause of the block is an uncommitted transaction, is there a way to commit it from QA? If I issue a KILL on the offending spid, the transaction is effectively rolled back, which I don't believe we want.
P
-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x74914743.[Email address protected]
Sent: Thursday, May 27, 2004 11:03 AM
To: LazyDBA.com Discussion
Subject: RE: SQL Server not releasing locks
Hi!
Maybe this thoughts would help :
These locks must go away if the transaction is committed or rolled back. The same with the service pack applied because the escalacion-lock-bug-fix addressed the problem that locks where held to the end of the transaction instead correctly to the end of the statement.
When there are locks there must be a transaction. (this holds even for shared locks in read commited mode which only live as long as the statement even if you dont started a transaction, because a single statement is implicitely running in a transaction )
It is possible that you did the following in an application:
1. Open a connection
2. BeginTransaction ( in ADO objConn.BeginTran() )
3. Executed a statement which for example updates some data on this connection( locks must then be held until the end of the transaction )
for example with (in ADO: objConn.Execute (update theField from theData where theRow = 'rowValue')
4. ..... do anything but do not one of the 3 things:
*) Commit or Rollback the transaction
*) Close the connection
*) Termiminate the process which opened the connection
Then you woul see what you see now.
If you have the process id then it is possible to see if there open transactions on that process with the query (there are other ways too) :
select open_tran, *
from master.dbo.sysprocesses
where spid = <theProcessID>
In your you application you have a connection, in Sql server this connection is assigned to a process so the process id corresponds with your connection.
best regards
FZ
-----Original Message-----
From: Lee David
[mailto:mssqldba-ezmlmshield-x8573214.[Email address protected]
Sent: Donnerstag, 27. Mai 2004 16:29
To: LazyDBA.com Discussion
Subject: RE: SQL Server not releasing locks
Paul,
There is a known issue with lock escalation in SP2. SQL server will not
always release the shared lock if it needs to escalate it.
http://support.microsoft.com/default.aspx?scid=kb;en-us;306908
Use the link above and then select the link half way down the page 'ALL
FIXES in SP3'; search that page for 'escalation' and you should find it.
We have also had ODBC timeout issues where the connection has timed out
during a transaction which can leave locks lying about.
Hope that helps
David Lee
Database Administrator
Carlson Marketing Group
-----Original Message-----
From: Schlieper Paul
[mailto:mssqldba-ezmlmshield-x53416424.[Email address protected]
Sent: Thursday, May 27, 2004 2:44 PM
To: LazyDBA.com Discussion
Subject: SQL Server not releasing locks
Hi all,
Using SQL2K SP2 on Win2K SP4.
Occasionally, we see a situation where SQL Server does not release locks
held by one connection, and which blocks other connections.
Using sp_who2, I can determine that the spid at the head of the blocking
chain is "Sleeping", Command is "Awaiting command". Speaking to the user
whose spid is at the head of the chain, they claim that everything looks
normal from their end.
We used to see quite a bit of this in the days of SQL 6.5, but very rare
since moving to SQL 2K.
Does anyone have any ideas what could be the cause, or how to go about
tracking it down?
P
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
This message and any attachment are intended for use only by the individual to whom they are specifically addressed above, and may contain privileged or
confidential information. Any unauthorized dissemination or copying of this message or any attachment, and any use or disclosure of any information contained in
them, is strictly prohibited and may be illegal. If you are not the intended recipient of this message, please notify the sender immediately and delete it from any
computer.
Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the company
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page