Implementation (concurrent data processing) that is not obtained by other users)

Data obtained by one user is not obtained by other users:

Problem:

When using ADO to access the database, take a certain record (such as 20 rows) from a table and use it in the program, delete records after use (no need to update or delete records ). In multi-user operations (each user uses the same operation), how can we ensure that the records selected by one user are not selected by other users?

Solution:

To solve this problem, you can add a flag column and set a flag for each user's record. New users can only retrieve records from records whose flag is not retrieved.

In this article, we will use transactions and locks to control data processing without adding any flag columns.

Example:

1. Create a test environment

Use tempdb Go Create Table DBO. Tb ( Id int identity (1, 1 ), Name nvarchar (1, 128 )) Insert Tb (name) Select Top 100 Name From syscolumns Go

2. Simulate 1st users

-- The query window sends the following query statement

Begin tran

-- The transaction is not committed or rolled back to keep the lock from being released

Set rowcount 20 Select * From TB with (updlock, readpast)

-- Updlock keeps the lock until the end of the transaction. readpast skips the locked data.

3. Simulate 2nd users (the statement is the same as that of 1st users, but is executed in another connection)

-- The query window sends the following query statement

Begin tran

-- The transaction is not committed or rolled back to keep the lock from being released

Set rowcount 20 Select * From TB with (updlock, readpast)

-- Updlock keeps the lock until the end of the transaction. readpast skips the locked data.

4. Results

You can see that query window 1 lists the first 20 data records, and query window 1 lists 21-40 data records.

In this case, different users need to fetch different data.

Note: After processing is complete, delete the record and submit the transaction.