Analysis of concurrency control and blocking protocol for database

analysis of concurrency control and blocking protocol for database

[Abstract] The database can be provided to multiple users to share data information resources, so the concurrency of transactions must be controlled, such concurrency control must introduce some blocking protocol to ensure the integrity of the data. This paper analyzes the problem of data inconsistency which can be produced in the concurrency operation of database, and gives the solving method.

[keywords] database concurrency control consistency blockade blocking protocol

First, Introduction

At present, the mainstream relational database usually allows multiple users to use and share simultaneously, so also has the mechanism of concurrency control, that is, to control the database, prevent multi-user concurrent use of the database caused by data errors and program run errors, to ensure the integrity of the data.

Second, the concept of transaction and concurrency control

When multiple users access data concurrently, multiple transactions are generated to access the same data at the same time, causing serious data errors and program run errors. So let's see, what is transactional and concurrency control?

A transaction is a user-defined sequence of database operations that is either done entirely or not, and is an inseparable, small unit of work. For example, in the SQL language, there are three statements that define a transaction:

BeginTransaction ;

COMMIT ;

ROLLBACK ;

Where the BeginTransaction is the token of the start of the transaction, and ends with commit or ROLLBACK, commit is used to commit all the operations of the transaction, and ROLLBACK is the time when a transaction fails to continue execution when a certain failure occurs during the operation The system undoes all the newly completed operations on the database in the transaction, scrolling back to the state at the start of the transaction. In order to make full use of the system resources, so that the shared resources of the database can be effectively utilized, multiple transactions must be executed in parallel, and the control of the parallel execution of the database is concurrency control.

Iii. data inconsistency issues that may arise from transactions for concurrent operations

For various reasons, the data of the database can be destroyed, for example, when multiple transactions run in parallel, the operation of different transactions produces cross-execution, or the transaction is forcibly stopped or interrupted during operation. As a result, transactions are likely to cause inconsistent data when it comes to concurrent operations, so let's look at a specific example. For example, the online sales system for airline tickets, if you have the following sequence of operations:

1. A ticket office (set to T1 service) to read out the number of tickets remaining a for a shift,

Set a=20

2. B ticket Office (set as T2 service) read out the remaining number of tickets in the same shift a, also 20

3. A ticket office (T1 business) sell one flight, modify the remaining number minus one (a←a-1), write a=19 back to the database

4. B Ticket Office (T2 business) also sell a ticket, modify the remaining number minus one (a←a-1), write a=19 back to the database from these operations, we see that the B ticket Office modified data covered by a ticket office modified data, the actual occurrence of two ticket sales, and the database is mistakenly deposited 19, A missing one. See Figure 1 for more information. This is the first case where the concurrency operation causes inconsistent data, called missing modifications (Lost Update), and the second is non-repeatable (non-repeatable read).

The third is to read "dirty" data (dirtyread).

Look at the second case, non-repeatable read refers to the transaction T1 read data, T2 perform the update operation, so that T1 can not reproduce the original read data, get with the last different results, example 2.

Read "Dirty" data refers to T1 to modify a data and write it back to the database, T2 read the same data, T1 for some reason was revoked, T1 perform rollback, restore to the original data, T2 enrolled in the process of an obsolete data, this data is a kind of garbage data, called "dirty" data, is also incorrect. See Figure 3.

From the above example, we see that the main reason for data inconsistency is that concurrent operations do not have a certain isolation of transactions, so the correct scheduling should make one user's transaction not be disturbed by other transactions, thus avoiding the inconsistency of data.

Iv. the main method of resolving inconsistent concurrency control of data by blocking protocol in concurrency control is blocking (Locking). is to dispatch concurrent operations in the correct way so that a user's transaction is not interfered with by other transactions during execution, thus avoiding inconsistencies in the data. Blocking is the ability of a transaction to have some control over the data it wants to manipulate. Blocking usually has 3 links: The first step is to apply for a lock, that is, the transaction before the operation of the data it will use to make a lock application; The second step is to obtain the lock, that is, when the condition is ripe, the system allows the transaction to lock the data, thus the transaction obtains the data control

The third step is to release the lock, which means that the transaction discards the control of the data after the operation is completed.

There are two basic types of closures:

1. Exclusive Lock (Exclusive Locks, abbreviation x Lock)

Exclusive locks or write locks are also called lock locks. Once the transaction T adds an exclusive lock (X Lock) to the data object A, only T is allowed to read and modify a, and any other transaction can neither read nor modify a, nor can it add any type of lock to a, until T releases the lock on A.

2. Shared Lock (Share Locks, abbreviation S lock)

A shared lock is also called a read lock. If the transaction t adds a shared lock (s lock) to the data object A, the other transaction can only have a plus s lock, and cannot add an X lock until the transaction T releases the S lock on a. When data is locked, it is also necessary to contract and execute rules and protocols, including when to apply for locks, time to hold locks, and when to release them, which are called blockade agreements (Locking Protocol), which are divided into the following three levels:

(1) A first-level blockade agreement. The first-level blocking protocol is that transaction T must have an X lock on the data before it is modified until the end of the transaction is released.

(2) Level two blockade protocol. The second-level blocking protocol is the transaction T to modify the data must be preceded by an X lock, until the end of the transaction to release the X-Lock, the data to be read must first add S lock, after reading can release S lock.

(3) Level three blockade protocol. The three-level blocking protocol is that transaction T must have an S lock on the data before it is read, and an X lock must be added before the data can be modified before all locks are released until the end of the transaction.

Once the blockade protocol has been implemented, problems caused by inconsistent data in database operations can be overcome.

From the case of Figure 4 we see that the transaction T1 in the execution of the process to occupy and add X lock, until after processing and then release the lock, T2 although also need to use, but under the constraints of the blockade protocol, the T2 required by the X lock is rejected, so must be in a wait state, until T1 released, T2 only access to the right, This eliminates the use of conflicts and avoids data loss. Here we see that the first level of blocking protocol is actually implemented here.

Figure 5, can be clearly seen, due to the implementation of the blockade protocol, the transaction T1 use a shared lock to occupy a A, a two block of data, so that the T2 need to add the X lock can not be implemented, (if it is the S lock, although it is possible to add, but also not able to modify the data, just read the data. When the T1 release the lock, the T2 can get and use the lock, so that the reading of the data B is still 100, does not affect the results of a+b, which is repeatable read. So we see, in fact, here is the level three block protocol see Figure 6, transaction T1 before the data C modification, the first to add an X lock, modified to write back to the database, then the T2 request on C added S lock, because T1 added x lock, T2 had to wait, when T1 for some reason revoked the modified data, C Recovered the original data 100, and so T1 release X lock after T2 to obtain the S lock on C, read or c = 1 0 0, so avoid reading "dirty" data. This is actually a level two lockdown protocol.

Through the above content, the database due to the use of a certain blocking protocol to avoid the inconsistency of data, which makes the concurrency control of the database is effective and beneficial, so that many transactions can be parallel operation of the database shared resources. This is the database reasonable scheduling, avoid the conflict, avoid the inconsistency of data.

Reference documents:

[1] Miashelland: Principles and applications of database systems [M]. Beijing: Machinery Industry Press, 2004

[2] Beauty Mary Pyefinch: I hope the book Room will be translated [M]. SQL database development from beginner to fine [m]. Beijing hope Electronic Press, 2000[3] Dingbaokang Dong Sound: Database use tutorial [m]. Beijing: Tsinghua University Press, 2001

Analysis of concurrency control and blocking protocol for database