Timestamp application (solve concurrency problem)-optimistic lock and pessimistic lock "turn"

This data type represents a binary number that is automatically generated, ensuring that the numbers are unique in the database. Timestamp is generally used as a mechanism for adding a version stamp to a table row. The storage size is 8 bytes.

A table can have only one timestamp column. The values in the timestamp column are updated each time the row containing the timestamp column is inserted or updated. This property makes the timestamp column unsuitable for use as a key, especially when it is not used as a primary key. Any update to the row changes the timestamp value, which changes the key value. If the column belongs to a primary key, the old key value will not be valid, and the foreign key referencing the old value will no longer be valid. If the table is referenced in a dynamic cursor, all updates change the position of the row in the cursor. If the column is an index key, all updates to the data row will also cause an index update.

Timestamp columns that are not nullable are semantically equivalent to the binary (8) column. Nullable timestamp columns are semantically equivalent to the varbinary (8) column.

In the actual multi-user concurrent access production environment, we often have to keep the data consistent as much as possible. And the most typical example is that we read the data from the table, check the validation, modify the data, and then write back to the database. In the process of reading and writing, if in a multiuser concurrency environment, other users have put the data you want to modify
Modification is a very likely occurrence, which results in inconsistency of data. To solve the problem, SQL Server puts forward the concept of optimistic locking and pessimistic locking, and below I use an example to illustrate how optimistic locking and pessimistic locking can be used to solve such problems.

/* Set up Test table: Train_ticket, representing a real train ticket library for users to register. The user wants to buy an unused train ticket from inside, that is to s_flag=0 ticket, register to the User: Update T_name,t_time,s_flag field. If two users update a ticket at the same time, it is intolerable, which is what we call inconsistent data lines. */

CREATE TABLE Train_ticket (t_no varchar (), t_name varchar (), S_flag bit,t_time datetime)

Pessimistic lock solution

Begin Tran
Select top 1 @TrainNo =t_no
From Train_ticket with (UPDLOCK) where s_flag=0

Update Train_ticket
Set T_name=user,
T_time=getdate (),
S_flag=1
where t_no= @TrainNo
Commit

Notice the difference? With (Updlock), yes, we used the WITH (updlock) option in the query, we added an update lock to the record when we queried the record, indicating that we were about to update the record. Note that updating and sharing locks is not a conflict. That is, other users can also query the contents of this table, but the update lock and exclusive lock are conflicting. So the other updates are blocked. If we execute this code in another window, we do not add the waifor delay clause. After execution on both sides, We found a successful registration of two train tickets. Maybe we've found the downside of pessimistic locking: When a user makes an updated transaction, other update users must wait in line, even if that user is not updating the same record.

Optimistic locking solution

--First, we add a column of T_timestamp in the Train_ticket table, which is the varbinary (8) type. But this value automatically grows at the time of the update.

ALTER TABLE Train_ticket add T_timestamp TimeStamp NOT NULL

--   acquisition number and original timestamp value
         select top 1 @TrainNo =t_no,
& nbsp;                      @timestamp =t_timestamp
        from train_ticket
         where s_flag=0
        &NBSP
        --   delay 50 seconds to simulate concurrent access.
          waitfor DELAY ' 000:00:50 '

        --   Purchase tickets, but to compare whether the timestamp has changed. If there is no change. Update is successful if a change occurs .
         update Train_ticket
          set T_name=user,
             t_time= GETDATE (),
             s_flag=1
          where t_no = @TrainNo F_timestamp = @timestamp

Set @rowcount =@ @rowcount
If @rowcount =1
Begin
print ' successful! '
Commit
End
else if @rowcount =0
Begin
if exists (select 1 from train_ticket where t_no = @TrainNo)

Begin
print ' The ticket was already buyed. '
Rollback Tran
End
Else
Begin
print ' This ticket doesn ' t exist! '
Rollback Tran
End
End

I've covered the use of optimistic locking and pessimistic locking in detail, in the actual production environment, if the amount of concurrency is small, we can use pessimistic locking method, because this method is very convenient and easy to use. But if the system's concurrency is very large, pessimistic locking can cause very large performance problems, So we need to choose the optimistic locking method.