Lock request timeout

Question

Lock request timeout

Bob sql 476

Hi All,

Appreciate you help on below.

End users are experiencing "Lock request timeout" very often and we are getting blame as database is unstable.
Need some guidance on how to go about troubleshooting and alleviate such concurrency issues. We do see some blocking but we don't see any long term blocking.

Error shared by app team :
SIP-10322 Cannot lock table [xxxxxxxx] because the table is locked by another process.

Is this because of too many concurrent sessions accessing an object or long transaction?
App users have increased the timeout value from front end. However they still seeing Lock request timeouts.
Any perfmon counters to be looked at ? what steps to be taken to build a story of why there are seeing the lock timeouts.

Best Regards,
Bob

Dan Guzman 9,401 Reputation points

2021-02-03T13:39:10.347+00:00

You mentioned blocking but no long-term blocking. Note that any blocking longer than the specified lock timeout will result in the error so you need to dig into the lock waits to identify the cause.

Accepted answer

2 additional answers

Your answer

Dan Guzman 9,401 Reputation points

2021-02-03T13:39:10.347+00:00

You mentioned blocking but no long-term blocking. Note that any blocking longer than the specified lock timeout will result in the error so you need to dig into the lock waits to identify the cause.

Answer 1

Tom Phillips 17,771

That error is not a SQL Server error. It appears to be an Informatica MDM application error. This may have nothing to do with SQL Server.

Please see:
https://knowledge.informatica.com/s/article/499012?language=en_US

Bob sql 476 Reputation points

2021-02-03T15:12:57.37+00:00

Hi Tom,

Thanks for the response and the link.

The table is in SQL Server and they connect to SQL Server to access the table. Does SQL Server locking doesn't come into picture? Trying to understand how it is not a SQL issue. Did you mean, that the default locking behavior changes when certain parameters are SET from the front end application? trying to understand how can any 3rd party application can override the locking and behavior of SQL Server.

Best Regards,
Bob
Tom Phillips 17,771 Reputation points

2021-02-03T15:36:22.157+00:00

That error is not a SQL Server error. So there is no telling how that error is generated. It may be a logical lock from the application, not a physical lock you will see in SQL Server.

Your first point of contact should be the application vendor, Informatica. They would best help diagnose the issue in their application.
Bob sql 476 Reputation points

2021-02-03T17:57:27.967+00:00

Thanks Tom. Now it makes sense.
tibor_karaszi@hotmail.com 4,316 Reputation points

2021-02-04T07:46:38.757+00:00

See my an Erland's reply. It is IMO likely that the error is indeed rooted in an SQL Server error, passed on by the app. We believe so because of the words "Lock request timeout".
Dan Guzman 9,401 Reputation points

2021-02-04T12:41:23.287+00:00

@Tom Phillips , this could be a SQL Server error if Informatica issues a SET LOCK TIMEOUT to override the default value of -1 (infinite). The SQL Server error in that case is error number 1222, "Lock request time out period exceeded", but the app might display different text.
Tom Phillips 17,771 Reputation points

2021-02-04T13:37:34.663+00:00

That might be true. However, it is still best to contact the application vendor to determine how to resolve "application" error messages.

Answer 2

tibor_karaszi@hotmail.com 4,316

If this is indeed an SQL Server error, then it is somewhat mangled by the application.

If might be error 1222, which means the developer asked for a timeout after being blocked x ms. All that would tell us is that you have blocking going in longer than what the developer though would be reasonable.

Blocking need to be dealt by at the client/app side. Them claiming SQL server being "unstable" tell us that they want to push responsibility away from them.

Sorry to say, but you need to get the app developer/vendor to be more helpful.

Unless you are doing other stuff in the database, stuff not expected by the app developers, of course. Like running reports off their database. Then your stuff might be the reason for the app being blocked.

Bob sql 476 Reputation points

2021-02-03T18:01:20.407+00:00

Thanks for sharing your thoughts Tibor.

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

I need to correct my colleagues a little bit. The error SIP-10322 is indeed not an error from SQL Server. However, "Lock request timeout" seems like error 1222, which only can occur is the process runs the command SET LOCK_TIMEOUT with a value >= 0. That is, by default, SQL Server waits forever.

But to tell what is actually is going, it certainly helps to know what Informatica is doing.

Share via

Lock request timeout

2 additional answers

Your answer