sqlserver--transactions-Summary of locks and quarantines

The lock prompt specifies the SQL statement, which overrides the isolation level of the transaction. The following is a breakdown of the individual locking tips (see the online Help for SQL Server for more information), which I have made.

④updlock: Issue an update lock, which is persisted to the end of the transaction transaction. (Update Lock: does not block anything else, allowing other things to read data (that is, the update lock is compatible with a shared lock), but he ensures that the data has not been updated since the last time the data was read)

In many systems, this is often the case, to keep a number unique, such as the number of vouchers in the accounting software. A number is processed so that the maximum number in the table is saved to the table, and then added to the number to form a new number. This process is very demanding for concurrent processing, so let's simulate this process to see how to keep the numbering unique.

Create a new table code to hold the maximum number of vouchers. The fields are as follows: No.: BH (Numeric (18,0)), voucher table name Pinzheng (varchar (50))

Let's say that there is a record in the table:

Bh	Pinzheng
18000	Accounting vouchers

Create a new stored procedure to generate the voucher number, as follows:

CREATE PROCEDURE UP_GETBH as

Begin Tran

Declare @numnewbh Numeric (18,0)

Select @numnewbh = BH from Code with (updlock,rowlock) where Pinzheng = ' Accounting voucher '

Set @numnewbh = @numnewbh + 1

Update code Set BH = @numnewbh where Pinzheng = ' Accounting voucher '

Print @numnewbh

Commit Tran

GO

Then, open the Query Analyzer, and open a few more connections (the author opened 8 connections, the simulation of 8 people simultaneously, the reader can open more connections to experiment), and similar to the following statements copied into each connection window,

DECLARE @i numeric (18,0)

Set @i = 1

While @i = 1

Begin

If GETDATE () > ' 2004-07-22 14:23 '--set a time to execute the UPGETBH stored procedure at the same time

Set @i = 0

End

EXEC UP_GETBH

Then, running each connection in succession, at the moment of 2004-7-22 14:23, each connection runs UP_GETBH at the same time. From the running results you can see that the connection sequence appears with a 18001-digit number, and there is no sign of the number or the phenomenon of missing.

Analysis: Because the SELECT statement in UP_GETBH uses update locks, because the update locks are incompatible, each connection waits until all other connections release the lock to execute, and the release of the update lock waits until the end of the transaction, so that no error occurs.

Attached: Compatibility table for Locks

The existing licensing model

sqlserver--transactions-Summary of locks and quarantines