SQL Server can concurrently obtain the maximum document number in the subscription database to test and solve the bill !, SQL Server Act

SQL Server can concurrently obtain the maximum document number in the subscription database to test and solve the bill !, SQL Server Act

Note:

Many transaction systems require document numbers, and it is a headache to obtain document numbers concurrently in order !~

 

The general document number format is: prefix + date + number (for example, kk2015050000001)

 

Now, in a simulated test, the document number in the database is processed as a table.

Database document number table (Billnumber) stores a total of one record for each account and its largest document number

The document number is a combined string with the last six digits as the serial number.

Stored Procedure for obtaining document numbers:

EXEC [dbo]. [GetBillnumber_Test] @ Account = 'account', @ Billnumber NVARCHAR (20) OUTPUT

 

Stored Procedure functions:Pass the user account and return the maximum document number of the user.

Internal logic of the stored procedure: (both query and update use clustered index search)

A. Find the document number of this account in the table

B. If the document number exists, extract the character after it and convert it to a number plus 1 to generate a new document number. Then, update this record to the latest document number.

C. If the document number does not exist, insert a new document number for this account for the first time and return the document number

However, concurrent operations may cause congestion and deadlocks. The transaction level of the current database is read committed ).

The common operation is to add a lock and keep the lock to the end of the transaction when the document number in the logical query table of the stored procedure is (as shown in. Here I am using WITH (UPDLOCK, HOLDLOCK). There is also an addition WITH (XLOCK, PAGLOCK) on the Internet. This locking method will make the entire page of Data inaccessible.

To facilitate the test in SQLqueryStress, I changed the stored procedure:

EXEC [dbo]. [GetBillnumber_Test] @ Account = 'account'

 

That is, no document number is returned, and the document number is thrown at the end of the stored procedure, so that SQLqueryStress can obtain and view the document number.

DECLARE @ re nvarchar (100)

DECLARE @ billNumber NVARCHAR (100) -- assigned document number (not described in the assignment process)

SET @ RE = n' [billNumber = '+ @ billNumber + N'] [SPID =' + CONVERT (VARCHAR (10), @ SPID) + N']'

RAISERROR (@ RE, 16,1, @ billNumber) -- Throw

Open two SQLQuerySrees windows and set the connection parameters:

The first window opens A connection, with each connection executed 500 times. The account is

The second window opens A connection. Each connection is executed for 500 times. The account is.

Enable deadlock tracking in the database:

-- Enable deadlock tracking

Dbcc traceon (1222,-1)

Dbcc tracestatus

Run the test]

Run the first and second SQLQuerySrees windows quickly (click "GO ")

After the execution is complete, click SQLQuerySrees to view the log:

Test results:

It is found that the document numbers are incremental, but there is a jump in the middle, because another process is also getting the document number. Check SQL server logs. No Deadlock is displayed!

The same is true for opening several SQLqueryStress tests. The result is normal and correct!

 

 

But! Question !~ When an updatable subscription is created in the database and the database is subscribed to, a deadlock will occur !~

Create updatable subscriptions for this table:

As shown in the preceding steps, the document number of the database subscription is obtained.

 

When executing SQLQuerySrees, you can also quickly return to the database to view the lock status. Reference Script: a script used by SqlServer to check the current lock request

As you can see, when updating the BillNumberRecord table, a spid also triggers data input to be inserted into the synchronization queue table. The range RangeS_U and RangeX_X are used for the document table. Check SQL server logs now,

Three machine blocking statements involved in the deadlock stored procedure:

Sp_MSdel_dboBillNumberRecord: (deadlock victim)

Delete [dbo]. [BillNumberRecord]

Where [Guid] = @ pkc1and [Account] = @ pkc2andmsrepl_tran_version = @ msrepl_tran_version

Trg_MSsync_upd_BillNumberRecord:

Update [dbo]. [BillNumberRecord] setmsrepl_tran_version = @ c7

Where [Guid] = @ c1 and [Account] = @ c2

EXEC [dbo]. [GetBillnumberBySID_Test]:

Operation: update the document number column of the document form as the maximum document number (the statement is not displayed here)

The resource list is as follows:

--Deadlockvictim = processe988e0

Resource-list

Keylock objectname = BillNumberRecordindexname = clustered index id = locke1193c0mode = RangeX-X

Owner-list

Owner id = processcdac70mode = RangeX-X

Waiter-list

Waiter id =Processe988e0Mode = UrequestType = wait

Keylock objectname = BillNumberRecordindexname = primary key (non-clustered index) id = lock58d986c0mode = U

Owner-list

Owner id =Processe988e0Mode = U

Waiter-list

Waiter id = processcdac70mode = UrequestType = wait

Next, execute the execution plan in the database to check which statements are processed:

EXEC [dbo]. [GetBillnumber_Test] @ Account = 'account'

 

The results are as follows:

A. First, find the current document number of this account (clustered index search) (UPDLOCK, HOLDLOCK)

B. If the document number exists, a new maximum document number is generated and updated back to the table (clustered index search and update)

C. Scan the pseudo table inserted (clustered index) in the trigger trg_MSsync_upd_BillNumberRecord.

D. Scan the pseudo table deleted (clustered index) in the trigger trg_MSsync_upd_BillNumberRecord.

E. Trigger trg_MSsync_upd_BillNumberRecord to update msrepl_tran_version (index search, update clustered index)

F. Finally, insert the synchronized transactions and commands into the queue table.

Only trg_MSsync_upd_BillNumberRecord is available for index search. Use the primary key non-clustered index (guid, account) to search and update the clustered index (account ).

 

This is a design issue. As the primary key in synchronization, it is better to also clustered indexes. The primary key of the table will not be changed during synchronization triggering, while synchronization often triggers changes, the primary key is not a clustered index and will be changed frequently. But now the data is being synchronized and cannot be changed. You can only use other methods. When there is no synchronization, the concurrency is normal and there is no deadlock. There is no need to change the stored procedure of the original document number.

 

Since it is caused by the following statement, you can change the index.

Update [dbo]. [BillNumberRecord] setmsrepl_tran_version = @ c7

Where [Guid] = @ c1 and [Account] = @ c2

The final solution to the deadlock is:

Change the synchronization trigger [dbo]. [trg_MSsync_upd_BillNumberRecord]

 

Comment out the change statement in the trigger.

Update [dbo]. [BillNumberRecord] set msrepl_tran_version = @ c7

Where [Guid] = @ c1 and [Account] = @ c2

 

Set a new update method and add clustered indexes. In this way, you can use clustered index search and update the clustered index!

Update bsetmsrepl_tran_version = @ c7

From [dbo]. [BillNumberRecord] asbwith (index = IX_BillNumberRecord)

Where [Guid] = @ c1 and [Account] = @ c2

 

During the test, more than a dozen SQLQuerySrees are opened, except for several other accounts, all of which are the same account.

Here we want to explain why we have opened more than a dozen.

Because we need to simulate a dozen accounts to operate simultaneously. Although SQLQuerySrees has two options: number of iterations and number of threads.

Iterations:Is the number of repeated executions

Number of threads:Is the number of new database session connections created

 

The number of iterations and the number of threads only simulate concurrency. In order to simulate simultaneous operations at a certain time point, more is set. Although different threads are opened, they still appear to be created in sequence.

This is the status of lock resources viewed at a certain moment, as shown in figure

At this moment, only a session with spid = 199 is obtaining the document number. The range lock RangX_X is used, and the intention exclusive lock is applied to the table (only queries cannot be changed ), other threads query the document number and want to obtain the RangS_U lock, so they are waiting. As a result, no deadlocks will occur in the logs !!~ Synchronization is also normal !~ If the trigger is not changed, a deadlock occurs and the deadlock must be handled. The execution time is longer.

 

So far, this is the test for concurrently obtaining the maximum document number during synchronization.