February 4, 2002 at 8:20 am
I have a server running multiple copies of the same app (VB6), all talking through same class to the database. On distributed installation one process failed to commit a transaction and left it open causing a complete lockup. Trace shows other similar transactions updating and committing so I think the code is sound but for the offending transaction the commit just never arrived. Lock strategy is client side batch optimistic.
Anyone got any ideas?
Thx
February 4, 2002 at 11:17 am
I'd write a process to look for open transactions. If you find one, you can kill the conneciton and let the dba know. dbcc opentran will identify opn transactions. Of look for blocks and let the dBa know.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 4, 2002 at 11:37 am
Thanks Steve
I have no problem identifying the open trans once it occurs but it is in an overnight update process so it is not as simple as killing the spid. I need to find out how it happens and prevent rather than correct the situation.
Keith
February 4, 2002 at 1:35 pm
Maybe log an entry into a table when each app starts, then run a job that will look for rows with a missing close out entry - then have it look for open trans (you could log the spid too) and kill them.
Andy
February 5, 2002 at 4:55 am
OK, first can you give an idea of what the apps do. This will help me understand better what may help you. The reason I ask is the programmer before me created several small apps that occasionally had a similar issue. Ultimately thou I found that it really did not need to exist outside of SQL Server and I redid them as jobs (none of which have given me any trouble). I like to understand exactly what is going on before I comment.
February 5, 2002 at 5:04 am
The app is essentially a VB COM executable which runs a number of COM dlls. Each called dll is self-contained in terms of data access and may call others depending on the content of the data. There are >100 possible top level components and many sub-components.
At runtime there will be a number of these executables (20+) running concurrently to achieve the required throughput hence optimistic locking.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply