Sql-server – Will Blocking cause timeout issue

blockinglockingsql serversql-server-2008-r2timeout

When I check in sys.sysprocesses in SQL 2008R2, a number of blocking sessions are there. Most of them are insert, update, and delete statements, with lock-related wait types.

Will this cause time out issues? Can anyone confirm whether this will cause time out / performance issues?

Best Answer

There are two type of timeouts.

One is when the client application (API) aborts the batch after a certain amount of time. Called "attention" in a SQL server trace. Default in many APIs is 30 seconds.

The other is when the client requests that SQL server abort the query with error 1222 after a certain time. SET LOCK_TIMEOUT. Default is indefinitely.

Yes, if a blocking lasts longer than any of above timeouts values, then you will get a timeout.

Do the user consider waiting for a long time (blocking) and perhaps even getting a timeout as bad performance. Yes, absolutely.