SQL Server error: The lock request time-out period has been exceeded. (Microsoft SQL Server, error: 1222)

In SSMs (Microsoft SQL Server Management Studio), when you view the table for the database, you encounter the "Lock Request time out period exceeded. (Microsoft SQL Server, error 1222) ", the corresponding Chinese error prompt is" the lock request time-out period has been exceeded. (Microsoft SQL Server, Error: 1222) ", as shown below, whether it is an account with general permissions or a login with the sysadmin role.

This error is a bit strange, check the database server monitoring blocked alarm messages, found blocking alarm, I use the following SQL statement to view, as follows

As shown above, a statement with a session ID of 65 executes TRUNCATE TABLE [ESQ_ITEM_PRICE_FOR_DCA], which blocks sessions with session ID 60, while a session with a session ID of 60 is YOURSQLDBA updating statistics

SET NOCOUNT on  ; with     tablesizestats as   (   select      Object_schema_name (ps.object_id, db_id (' ODS ')) as SCN--collate chinese_prc_ci_as  , object_name (Ps.object _id, db_id (' ODS ')) as TB--collate chinese_prc_ci_as  , Sum (ps.page_count) as pg  from    Sys.dm_db_index_physical_stats (db_id (' ODS '), NULL, NULL, NULL, ' LIMITED ') ps  group by      ps.object_id   )   Insert into #tableNames (SCN, TB, seq, sampling)   select  & nbsp;  scn , tb , Row_number () over (Order by SCN, TB) as seq , CASE    &N bsp;  when PG > 200001 then  ' ten '       when PG between 50001 and 200000 then  ' 20 '       when Pg between 5001 and 50000 then  ' + '       else  ' + '     End    from     tablesizestats  where (ABS (checksum (TB))% 1) = 0   

It blocks sessions with a session ID of 68

SELECT COUNT (1) from [ESQ_ITEM_PRICE_FOR_DCA]

In this case, there are two more confusing places:

One: The session ID 65 process is in the sleeping state, and the session is executing the TRUNCATE statement, supposedly truncate should be executed very quickly. It's strange that a truncate session is in the sleeping state, and this session is a request from a Linux server Talend application. Then there is only one possibility that the TRUNCATE statement is inside the transaction, and the transaction has not been committed or rolled back for logical reasons.

Two: The SQL blocking statement actually led to the above "Lock Request time out period exceeded." (Microsoft SQL Server, error 1222) ".

As for the above two problems, we can construct a case to see that the following steps can be used in testing the database test to re-make the error:

Conversational Sentence 1:

BEGIN TRAN

TRUNCATE TABLE TEST;

--rollback;

Conversational Sentence 2:

UPDATE STATISTICS dbo. TEST;

Conversational Sentence 3:

as shown above, session 52 is in the sleeping state. then you go to SSMs and look at the table and you'll encounter this "the lock request timeout period has been exceeded." (Microsoft SQL Server, Error: 1222) "error. As for the actual application Talend, it is unclear for what reason the transaction was not committed or rolled back. This example perfectly demonstrates and reproduces the problem

SQL Server error: The lock request time-out period has been exceeded. (Microsoft SQL Server, error: 1222)