June 9, 2022 at 8:29 am
My short question: how can I identify a transaction that has no active client? That will never become committed and remain open until a server restart? I am after a way to gracefully handle these.
My long question: I have a multi-tier application that has a client that runs on a VMware pc. It connects to a server that in turn connects with it's own sessions to the database. There are situations where the client ends (crash, user X-closes the app, network outage, etc) but the server retains the session and transactions to the database.
The application internally maintains "application locks", being a update to a field in the table, so that users can't all try to edit the same record. These are in addition our traditional "transaction locks". At some point in the past the vendor provided a predecessor of mine a nightly script to reset the "application locks" back to NULL, thereby making the records available to other users the following day.
What is happening is that a user edits a record, sets the "application lock", then the client dies. The "application lock" and "transaction lock" remain in play. Then the nightly job kicks off to reset all the "application locks", hits the "transaction lock" and waits. It has taken out a table lock that then blocks all users the following day. My phone rings, I identify the session and user, kill the session and call the user.
I realize this is an application issue however a vendor fix is not possible. They have provided an updated nightly script that skips any "transaction lock" issues using a cursor and "with (rowlock,readpast)" to reset the "application locks" with taking a table lock. But this then leaves the orphaned transaction alive until a server restart.
So...what is a graceful way to handle this situation?
June 10, 2022 at 9:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 10, 2022 at 10:55 am
One graceful solution could be for the vendor to address the issue they caused. Another graceful way could be to rewrite the application so the DB Engine manages locks and not the client application (which is inherently problematic imo). Since "graceful" might not be in the cards maybe you could define the attributes of a "hung transaction" (sounds like you've got a process already) and automate the killing of offending spids. Not very graceful tho 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply