May 25, 2020 at 11:18 am
Hi,
We are getting lock request time out period exceeded from application side. Transaction are failing to due Lock request time out.
ERROR:-
Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
; SQL []; Lock request time out period exceeded.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time out period exceeded.
ERROR [] [] Error in catch block of execute method of SapXmlUploadWebScript class:- java.lang.Exception: Error in setPermission method of SapXmlUploadWebScript class: -
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Lock request time
Upon checking, one open sessions in a Sleeping status that were holding locks inside of the Engine since long time. If we kill the sleeping session all pending request completed immediately. Please suggest how to solve this issue.
Thanks in advance!!
May 25, 2020 at 2:49 pm
Can you identify the query holding the lock so it can be optimized? It won’t be possible to give useful advise without more information. Once the query is isolated I would look at covering indexes, and ways of optimizing the problematic SQL.
May 25, 2020 at 5:21 pm
One guess on this - are you closing the connection and committing the transaction in your application code?
When I've seen "stuck transactions" like that, it is often caused by the application code opening a transaction and failing to commit it.
Where I work, we try to keep our application code and the associated SQL connections open for a minimum amount of time. So when SQL calls are needed, we open the connection, run the query, and close the connection. If a transaction is opened, it is committed or rolled back prior to closing the connection but we keep the connections open for short windows. This does add a delay into the application as it needs to go through extra steps, but as all of our applications are internal-only, the additional timing is under a second to complete.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply