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

Source: Internet
Author: User
Tags 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

SetNocount on; withTablesizestats as(SelectObject_schema_name (ps.object_id, db_id (' ODS ')) asSCN--CollateChinese_prc_ci_as, object_name (ps.object_id, db_id (' ODS ')) asTB--CollateChinese_prc_ci_as,Sum(Ps.page_count) asPg fromSys.dm_db_index_physical_stats (DB_ID (' ODS '),NULL,NULL,NULL,' LIMITED ') PsGroup byPS.OBJECT_ID) Insert into#tableNames (SCN, TB, seq, sampling)SelectSCN, TB, row_number () Over(Order bySCN, TB) asSEQ, Case whenPG > 200001 Then' Ten ' whenPgbetween50001 and200000 Then' A ' whenPgbetween5001 and50000 Then' A 'Else' + 'End fromTablesizestatswhere(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 comparison 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 the Linux server Talend.

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

On the second question, if the session that updates the statistics for the database table is blocked, it really causes the error above, we can construct a case to see that the following steps can be used in testing the database test to re-create the error:

Conversational Sentence 1:

BEGIN TRAN

TRUNCATE TABLE TEST;

--rollback;

Conversational Sentence 2:

UPDATE STATISTICS dbo. TEST;

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. But the second question is still not clear. Because it is not familiar to Talend, so it is difficult to comb from the inside clearly. But from SQL Server, I still do not understand why such a strange phenomenon!

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.