SQL Server concurrent processing-Use of transactions and locks

 

1. Transaction)
Transactions mainly take into account the security performance and correctness of data in exceptional circumstances. For example, a transfer program has several statements that execute different functions, and now the money is drawn from the first account, which causes program interruption due to other reasons, the second account did not receive the money, and the first account did not have the money, which is obviously wrong. To solve this kind of problem, DBMS puts forward the transaction concept. The preceding transaction solution is to take the preceding extraction and transfer as a whole to form an operation set. The operations in this set are either not executed or all are executed! Therefore, transactions are "Atomic", and transactions as a whole are inseparable.

Generally, SQL begins transactions implicitly. Of course, you can also start transactions explicitly. However, the end of the transaction must be explicit. There are two ways to end the transaction:

1. Commit (submit ). If you think that all operations have been completed, you can end the transaction and submit the transaction to the system. After the transaction is committed, all modifications will take effect. before the transaction is committed, all modifications can be voided.

2. rollback (rollback ). Rollback ends the current transaction and stops all operations since the start of the transaction and returns to the starting state of the transaction. Note that you can set some save points within the transaction so that you do not have to discard the entire transaction and roll back to the reserved point as needed.

We know that word and many software have the Undo function, and transactions are actually similar to the Undo function! So what is its working principle? It turns out that at the beginning of a transaction in DBMS, the data, type, and object of each of your operations are recorded at this time, and each operation corresponds to a corresponding record, when the transaction is completed successfully, you can clear these records. If an exception occurs and the transaction fails, you can reverse it and perform a reverse operation on all the records we saved. For example, a transaction contains

(1) Delete the first record

(2) Add a new record

(3) modified 5th records

(4 )...

In three steps, the DBMS records the information of each operation at the beginning of the transaction:

(1) Delete, first, record data

(2) Add and record data. Assume that a record number 8 is obtained.

(3) data before and after modification

If an error occurs during the execution of the transaction, assuming that an error occurs in the first sentence, the transaction needs to be rolled back, And the DBMS will execute the following action:

1. Modify 5th records and replace the modified data with the data before modification.

2. Delete 8th records

3. Add the first record

In this way, the data can return to the state before the start of the transaction, which is also the principle of the word and other software undo.

In SQL Server, many statements automatically start transactions implicitly. How can we start transactions explicitly? The command format is as follows:

Begin Tran [tran_name]

As mentioned earlier, you can set a save point for the transaction. The command is as follows:

Save Tran savepointname

Finally, the transaction can be committed or rolled back in the following format:

Submit: commit Tran [tran_name]

Rollback: rollback Tran [tran_name | savepointname]

The preceding Tran can also be written as transaction.

For example, change the student ID of 0000000 to 0001156. We have learned a question about how to use it to prevent student numbers from being inconsistent. This time we use transactions for processing.

-- This transaction aims to ensure the consistency of data (student ID ).

Begin Tran mytran/* start a transaction */

Update score/* update score table */

Set s_no = '000000'

Where s_no = '20140901'

If @ error <> 0

Begin

Print 'a error occur during update the table [score]'

Rollback tran

Return

End

Update idinfo/* update idinfo table */

Set s_no = '000000'

Where s_no = '20140901'

If @ error <> 0/* checks whether the update is successful, @ error returns the status of the previous SQL statement */

Begin

Print 'a error occur during update the table [idinfo]'

Rollback TRAN/* rollback */

Return/* exit this process */

End

Else

Commit Tran mytran/* commit transaction */

Ii. Concurrency Control
Locking)
Concurrency Control mainly takes into account that multiple users simultaneously access the same data in the database. For example, the ticket conductor A and B now have 10 tickets in the system, and a and B now get 10 tickets in the database. At this time, a sells one, the number of data records written back to the database is 9, and B also sells one, because he previously read 10, so he also writes back 9, so an error occurs, actually, there are only eight tickets! This problem is called "Write overwrite ". After analysis and research, there are several concurrency control situations (we do not consider the situation where both transactions are "read", so we do not need to consider ):

T1
T2
T1
T2
T1
T2

①
Read a = 10

Read a = 50

Read B = 100

A + B = 150

Read a = 100

A = A * 2

Write a (1, 200)

②

Read a = 10

Read B = 100

B = B * 2

Write B

Read a = 200

③
A = A-1

Write a (9)

Read a = 50

Read B = 200

A + B = 250

Check (incorrect)

Rollback tran

A = 100

④

A = A-1

Write a (9)

Write overwrite (lost modification)
Repeated read is not allowed.
Read "dirty data"

There is a specific term "dirty data" in the database to describe the data that has been changed by some transactions but has not yet been committed.

So how can users access data at the same time? You cannot restrict user operations because of concurrency! The solutions for concurrency control are locking and transactions. Transactions are the basic unit of concurrency control. A transaction is not equal to a program. A program can contain multiple transactions. Next we will discuss the blocking mechanism in detail.

From the database perspective, there are two types of locks: exclusive locks and share locks ). The X lock only allows operations on locked transactions. Other transactions do not allow locking or any operations (read or write). Other transactions must be unlocked before they can continue to run! The S lock allows multiple transactions to lock data at the same time. If transaction t locks the data R, other transactions cannot apply the X lock to the R, however, the S lock can be applied to ensure that r cannot be modified by other transactions. In addition, there is also a range of locks to consider. We can perform row locks, table locks, or even database locks. The larger the range of locks, the simpler the implementation, the lower the overhead, the lower the concurrent program of Data! On the contrary, if the lock range is smaller, the more complicated the implementation, the higher the overhead, and the higher the concurrency program of data. Generally, considering the cost and performance of locking, we recommend that you minimize the scope of locking for transactions that process a small amount of data and increase the concurrency of data, table locks should be used to prevent such attacks. In addition, when a transaction is completed, unlock as quickly as possible. There is also an update lock (U Lock) in SQL Server, which is compatible with the S lock. If a transaction wants to update data, it can take the U Lock, in the initial stage of the transaction, the data may be read. At this time, the S lock is used. Later, the data is modified. At this time, the S lock is automatically upgraded to the X lock. In SQL Server, the lock is automatically forced, but we should learn to effectively design the application. In general, we consider this: The s lock is used for reading and the X lock is used for writing.

From the programmer's perspective, there are two types of locks: Optimistic locks and pessimistic locks ). Optimistic locks are used by the database system to automatically implement lock management when processing data. We know from the past that SQL server adopts optimistic locks: For update, insert, delete uses the X lock automatically. For select, the S lock is automatically used. For pessimistic locks, the programmer needs to control the locking and unlocking actions.

Let's take a look at how to use the lock to solve the previous conflict:

T1
T2
T1
T2
T1
T2

①
Xlock

Read a = 10

Slock

Slock B

Read a = 50

Read B = 100

A + B = 150

Xlock

Read a = 100

A = A * 2

Write a (1, 200)

②

Request

Xlock

Waiting...

Request

Xlock B

Waiting...

Request

Slock

Waiting...

③
A = A-1

Write a (9)

Commit

Xunlock
Waiting...
Read a = 50

Read B = 100

A + B = 150

Check (OK)

Commit

Sunlock

Sunlock B
Waiting...
Rollback tran

A = 100

Xunlock
Waiting...

④

Xlock

Read a = 9

A = A-1

Write a (8)

Commit

Xunlock
Xlock B

Read B = 100

B = B * 2

Write B = 200

Commit

Xunlock B

Slock

Read a = 100

Commit

Sunlock

Write overwrite (lost modification)
Repeated read is not allowed.
Read "dirty data"

When using the lock, be sure to observe the following two items: (1) Lock first and then operate; (2) Unlock after the transaction ends. Finally, we will summarize how to use the lock to solve the above three problems (level 3 blocking Protocol ):

L level 1 blocking protocol-apply an X lock to the data R to be modified by transaction t until the transaction ends to prevent "Write overwrite" and ensure that t is recoverable.

L Level 2 blocking protocol-Level 1 blocking Protocol plus s lock on the Data R to be read by t to prevent reading "dirty data"

L Level 3 blocking protocol-Level 1 blocking Protocol plus s lock on the Data R to be read by t until the transaction ends, the problem of repeated read can be solved.

SQL Server automatically implements locks, but sometimes it is necessary to manually adjust the lock level. How can this problem be solved? Both SQL Server and Delphi adopt the isolation level. There are four isolation levels in SQL Server:

1. Read committed
Similar to the S lock, the SELECT command does not return uncommitted data or dirty data at this isolation level. It is the default isolation level of SQL Server;

2. Read uncommitted
In contrast to the Read committed isolation level, it allows reading data that has been modified by other users but has not yet been submitted for determination, with the minimum limit level;

3. Repeatable read
Data read by the SELECT command at this isolation level will not be changed during the entire command execution process. Other transactions cannot execute update or delete, but can insert. This option will affect the system performance. It is best not to use this isolation level unless necessary;

4. serializable
This is the biggest restriction. Similar to the X lock, other transactions are not allowed to perform any write access. Do not use this option unless necessary.

The SET command must be used to set the isolation level. The syntax is as follows:

SET transaction isolation level
{Read committed | read uncommitted | Repeatable read | serializable}

You can use this command at the beginning of the transaction. This isolation level is valid for the SQL server connection (not the current transaction) until the next time you use this command to set a new isolation level.

Active locks and deadlocks
Next we will discuss the special cases of locks:

Suppose T1 needs to update R1 and R2. First, it locks R1, And the other transaction T2 also needs to update R2 and R1. First, it locks R2. At this time, t1 has to wait to lock R2, while T2 has to lock R1. At this time, t1 and t2 have to wait for each other to unlock, resulting in an endless loop, this is called a "deadlock ".

Let's look at another situation: T1 locks R and T2 requests to block R. At this time, it must wait. T3 also requests to block R. After T1 is unlocked, The T3 request is approved, so t2 must wait, and then T3 request to block R. After T3 is unlocked, T4 request is approved, so T2 continues to wait ,... this may lead to t2 infinite wait. This is called a "live lock". The solution to the live lock is relatively simple. You can use the service policy first.

Generally, the following policies can be adopted for deadlocks:

1. Lock the data requested by the transaction lock at one time. Otherwise, the transaction cannot run. Disadvantages: reduced concurrency;

2. Define a blocking order in advance, and lock all transactions in a certain order;

3. Do not take any measures to prevent the deadlock, but check whether there is a deadlock or remove the deadlock.

Deadlock is discussed in detail in the operating system course. We should minimize the possibility of deadlocks: the transaction should be as short as possible, the user should be prevented from interacting with the transaction, and the low isolation level should be used as much as possible.

In SQL Server, check and remove deadlocks: if the system detects a deadlock, a transaction is selected as the victim and the transaction is automatically terminated and rolled back, and the system returns a 1025 error to the application. Any program may be victim to the system. Therefore, any program should handle the 1025 error, otherwise, your application may not run properly. Oracle adopts the same policy. In general, SQL Server chooses to cancel the transaction with the least cost as the victim. If you want to specify the transaction level when a deadlock occurs in SQL Server, you can use the following command:

Set deadlock_priority {LOW | normal}

Low indicates that if a deadlock occurs, the current transaction is the first choice, and the normal table is processed normally. As discussed above, when the request is locked and cannot meet the request, the transaction will wait. The waiting period cannot be an indefinite period. We can set a waiting time after the specified time is exceeded, we think that a deadlock may occur, and the transaction will be automatically rolled back. The lock timeout can be set using the following command:

Set lock_timeout {-1 | 0 | n}

-1 indicates waiting for an indefinite period. Default Value: 0 indicates not waiting at all. N indicates waiting for N milliseconds. If the lock fails after N milliseconds, a locking error is returned.

In addition, @ lock_timeout indicates that the current lock timeout setting is returned. For example, we can use select @ lock_timeout to view the data.