SQL Server updatable subscriptions in the subscription library concurrency to obtain the maximum number of document numbers test and resolution ACT!

Description

Many transactions of the system need a document number, and document number in order to get the same thing is a headache! ~

the general Document number format is: prefix + date + number (for example: KK20150501000001)

Now for the simulation test, the document number in the database is treated as a single table.

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

The document number is a combined string, and the latter 6 bits are ordinal.

To obtain a stored procedure for a document number:

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

stored procedure features: Pass the user account and return the user's maximum document number.

Stored Procedure Internal logic: (queries and updates are used for clustered index lookups)

A. find the document number of the account in the table

B. If the document number is present, intercept the post-character to the number plus 1 to generate a new document number to return, and update this record to the latest document number

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

However, during concurrent operations, blockages and deadlocks can occur. The transaction level for the current database is read -committed ( committed).

The usual action is to store the document number in the Internal logical query Table of the procedure (such as above A.), plus the lock and keep the lock to the end of the transaction. I'm here for with(UPDLOCK,HOLDLOCK), and online there's an addition with (XLOCK,paglock) , this locking method will not be able to access the entire page of data.

To facilitate the sqlquerystress test, I changed the stored procedure to:

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

That is, the document number is not returned, and the document number is thrown at the end of the stored procedure so that sqlquerystress can obtain the view document number

DECLARE @RE NVARCHAR (+)

DECLARE @billNumber NVARCHAR (+) -- assigned document number (the assignment procedure is not described)

set @RE = n ' [billnumber= ' + @ Billnumber + n ']  [spid= '

RAISERROR (@RE,1,@billNumber) -- thrown

Open 2 Sqlquerysrees windows and set the connection parameters:

First window: Open 1 connections, execute 500 times per connection;

Second window: Open 1 connections, execute 500 times per connection;

The deadlock trace is turned on in the database:

--  turn on deadlock tracking

DBCC Traceon (1222,-1)

DBCC Tracestatus

"Execute Test"

Fast execution of the first, Second Sqlquerysrees window (click "GO")

When the execution is complete, click sqlquerysrees log to view:

Test results:

Notice that the document number is incremented, except that there is a jump in the middle because another process is acquiring the document number. View SQL Server logs and do not see a deadlock!

Open several sqlquerystress The same is true for testing. The result is normal and consistent!

but! Here's the problem! ~ When a updatable subscription is created in the database and the subscription database is operating, a deadlock is generated! ~

To create an updatable subscription to the table:

As in the previous steps, get the document number for the document table of the subscribing database.

When executing sqlquerysrees, also quickly return to the database to see the lock situation. Reference script: SQL Server A script to view the current lock request situation

As you can see, a SPID also triggers the insertion of data input into the synchronization queue table when updating the table Billnumberrecord. The Ranges_u and rangex_x of the scope are used for the document table. Now look at the SQL Server logs

3 blocked statements for the stored procedure that participates in deadlocks:

Sp_msdel_dbobillnumberrecord : (Deadlock victim)

Delete [dbo] . [Billnumberrecord]

where [Guid] = @pkc1 and [Account] = @pkc2 and msrepl_tran_version = @msrepl_tran_version

Trg_mssync_upd_billnumberrecord :

Update [dbo] . [Billnumberrecord] Set msrepl_tran_version = @c7

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

Exec[dbo]. [Getbillnumberbysid_test] :

The action is: the document number of the Update document table is listed as the maximum document number (the statement is not displayed here)

The list of resources is as follows:

-- Deadlockvictim=processe988e0

Resource - List

Keylock objectname = Billnumberrecord IndexName = Clustered Index ID = locke1193c0 Mode = Rangex - X

owner - List

owner ID = Processcdac70 Mode = Rangex - X

Waiter - List

Waiter ID = processe988e0 Mode = U RequestType = wait

Keylock objectname = Billnumberrecord IndexName = PRIMARY Key ( nonclustered index ) ID = lock58d986c0 Mode = U

owner - List

owner ID = processe988e0 Mode = U

Waiter - List

Waiter ID = Processcdac70 Mode = U RequestType = wait

Next, perform a view of the execution plan in the database and what statements are processed:

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

As a result, follow the internal logic of the stored procedure:

A. first find the current document number (clustered index lookup) for this account (UPDLOCK,HOLDLOCK )

B. Document number exists, generate a new maximum document number, update back to the table (Clustered index Find and update)

c. Scan pseudo-table inserted ( Clustered Index ), in the trigger Trg_mssync_upd_billnumberrecord in

d. scan Pseudo-table deleted( clustered index ), on trigger Trg_mssync_upd_ in Billnumberrecord

E. trigger trg_mssync_upd_billnumberrecord perform update msrepl_tran_version, (Index lookup, update clustered index )

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

Index Lookup can appear on the above, only Trg_mssync_upd_billnumberrecord up. Use the primary key nonclustered index (GUID,account) to find and update the clustered index (account).

This is a design problem, as the primary key in synchronization, preferably a clustered index, table primary key, in the synchronous trigger is not changed, and synchronization is often triggered changes, the primary key is not a clustered index, will be changed frequently. But now that the data is synchronized and cannot be changed, you can only use other methods. Without synchronization, concurrency is normal and there is no deadlock. There is no need to change the original document to obtain the number of stored procedures, and nothing can be changed.

Since the following statement is causing this, try to change the index.

Update [dbo] . [Billnumberrecord] Set msrepl_tran_version = @c7

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

The final resolution of the deadlock method is:

To change a synchronization trigger [dbo] . [Trg_mssync_upd_billnumberrecord]

Comments The change statement within the trigger.

UPDATE[DBO]. [Billnumberrecord] Set msrepl_tran_version = @c7

where [Guid] = @c1 and [account]= @c2

Sets the new Update method and forces the clustered index to be added. This makes it possible to use clustered index lookups and is a clustered index update!

Update b Set msrepl_tran_version = @c7

from [dbo] . [Billnumberrecord] as b with (index=ix_billnumberrecord)

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

the test opened more than 10 times. sqlquerysrees , except for a few other accounts, the rest are the same account.

Here are some explanations for why more than 10 are opened.

because we want to simulate more than 10 accounts simultaneously operation. sqlquerysrees Although there are two selection settings--iteration count and number of threads.

Number of iterations: is the number of repeated executions

Number of threads: is how many new databases are created Session Connection

The number of iterations and threads is only simulated concurrency, in order to simulate the simultaneous operation at a certain time to set more. Although different threads are open, they appear to be created sequentially.

This is the lock resource situation that is viewed at a moment, such as

only now spid=199 The session is getting the document number, using a range lock rangx_x and an intent exclusive lock on the table (only the query cannot be changed), while the other threads are querying the document number and want to acquire the lock Rangs_u , so all are waiting. As a result, there is no more deadlock in the log!! ~ sync is also normal! ~ And if there are no changes to the trigger, a deadlock occurs, and the deadlock is processed, the execution time is longer.

At this point, the test to get the maximum document number concurrently in the synchronization is here.

SQL Server updatable subscriptions in the subscription library concurrency to obtain the maximum number of document numbers test and resolution ACT!