Questions about X-lock-whether the SELECT+X lock is held to the end of the transaction

Preface: Read Song Sang's article "An accidental x lock does not block the problem", combined with my test, explain my use of the X lock in select will hold to the end of the transaction error;

Details not much to say, see Song Sang's "An accidental x lock does not block the problem" and "Lost shared lock", the select+x lock and select+s the situation of the lock is explained. The following describes only my tests, and the structure and data of the test tables are as follows:

1 /** * * * Script for selecttopnrows command from SSMS * * * **/2 CREATE TABLE [test_a].[dbo].[tmp_byxl_01](IDINT IDENTITY, flagint)3 INSERT  into [test_a].[dbo].[tmp_byxl_01](flag)VALUES(NULL)4 Go 75 UPDATE [test_a].[dbo].[tmp_byxl_01] SETFlag=ID6 7 SELECT TOP  + [ID]8,[Flag]9    from [test_a].[dbo].[tmp_byxl_01]Ten  One  A ------------------------------- - ID Flag - ----------- ---- the 1           1 - 2           2 - 3           3 - 4           4 + 5           5 - 6           6 + 7           7 A  at(7Row affected)

Since the case comes from the system renewal issue, the business is implemented by invoking the stored procedure, so each call is a select+x lock, which is not the same as the "select+x Lock and Select+s lock" mentioned in the above article.

First of all, my mistake.

MYTH: The x Lock specified in select will be released immediately after the query ends and does not last until Tran ends

The test code is as follows:

--session_aBEGIN TRANSELECT *  from [test_a].[dbo].[tmp_byxl_01](Xlock)WHEREFlag=2  WAITFORDELAY'00:00:10'COMMIT--Session_bBEGIN TRANSELECT *  from [test_a].[dbo].[tmp_byxl_01](xlock)  WHEREFlag=2COMMIT

Since two Tran are all applying for xlock, when executed, Session_a (spid=53) is executed first, Session_b (spid=55) is executed after approximately 5 seconds, sp_lock can be seen, spid=55 is blocked when applying for X lock

From the execution time, the SPID (55) is at the beginning of the SPID (53) and about 5 seconds, and the execution time is basically coincident.

This test validates the above pitfalls. The X lock on select continues to the end of Tran so that the same query (and xlock) of other processes can be blocked for such a long time;

For such cases, general application scenarios such as the merchant's recharge system, snapping system, etc.

The need to convert a large concurrency environment into a single process hold lock (select is for judgment, such as the account start amount cannot be negative, or the current product information is queried in case of oversold)

For this kind of problem, the individual thinks, increases the xlock to carry on the query, is in order to avoid the dirty reading effectively, although increases the pagelock way may avoid s lock the optimization question, but may cause the lock scope to be too large.

If there is no ordinary S lock query, do not add the Pagelock elevation lock level, but also can meet the large concurrency requirements.

Questions about X-lock-whether the SELECT+X lock is held to the end of the transaction