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

Source: Internet
Author: User
Tags microsoft sql server session id sessions management studio microsoft sql server management studio sql server management sql server management studio

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.