Timestamp application (solving concurrency issues) -- optimistic lock and pessimistic lock

This type of data represents the automatically generated binary number, which is unique in the database. Timestamp is generally used to add version stamps to table rows. The storage size is 8 bytes.

A table can have only one timestamp column. Values in the timestamp column are updated each time a row containing the timestamp column is inserted or updated. This attribute makes the timestamp column not suitable for use as a key, especially not as a primary key. Any update to the row changes the timestamp value to change the key value. If the column belongs to the primary key, the old key value is invalid, and the foreign key that references the old value is no longer valid. If the table is referenced in a dynamic cursor, all updates change the row position of the cursor. If this column belongs to the index key, all updates to the data row will also lead to index updates.

The timestamp column that cannot be null is semantically equivalent to the binary (8) column. The empty timestamp column can be semantically equivalent to the varbinary (8) column.

In the actual production environment where multiple users access data concurrently, we often try to maintain data consistency as much as possible. The most typical example is to read data from the table, check and verify the data, and then write it back to the database. In the process of reading and writing, if in the multi-user concurrent environment, other users have modified the data
Modifications may occur frequently, resulting in data inconsistency. To solve this problem, SQL Server puts forward the concepts of Optimistic Locking and pessimistic locking. I will use an example below to illustrate how to use Optimistic Locking and pessimistic locking to solve this problem.

/* Create a test table: train_ticket, which represents a real train ticket library for user registration. the user wants to purchase an unused train ticket from the inside, that is, the s_flag = 0 ticket. Register with the user: update the t_name, t_time, s_flag fields. if two users update a ticket at the same time, it is intolerable, that is, the data inconsistency line we call. */

Create Table train_ticket (t_no varchar (20), t_name varchar (20), s_flag bit, t_time datetime)

Pessimistic locking 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

Are there any differences? With (updlock), yes, we use the with (updlock) option during the query. When querying records, we add an update lock to the records, indicates that the next record will be updated. note that the update lock does not conflict with the shared lock, that is, other users can query the content of this table, but it conflicts with the update lock and the exclusive lock. therefore, other update users will be blocked. if we execute this in another windowCode, Do not add the waifor delay clause. after the execution on both sides, we found that two train tickets were successfully registered. we may have discovered the disadvantage of pessimistic locking: when a user performs an update transaction, other update users must wait in queue even if the user updates a different record.

Optimistic Locking Solution

-- First, we add a t_timestamp column to the train_ticket table. This column is of the varbinary (8) type, but this value will automatically increase during update.

Alter table train_ticket add t_timestamp timestamp not null

-- get the number and original timestamp value
select top 1 @ trainno = t_no,
@ timestamp = t_timestamp
from train_ticket
where s_flag = 0
-- the delay is 50 seconds, simulate concurrent access.
waitfor delay '000: 00: 50'

-- buy a ticket, but check whether the timestamp has changed. if not. update successful. if any change occurs, the update fails.
Update train_ticket
set t_name = user,
t_time = getdate (),
s_flag = 1
where t_no = @ trainno and 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 have introduced in detail the use of Optimistic Locking and pessimistic locking. In the actual production environment, if the concurrency is not large, we can use the method of pessimistic locking, this method is very convenient and simple to use. however, if the system concurrency is very large, pessimistic locking will bring about a very large performance problem, so we need to choose an Optimistic Locking method.