Database concurrency control.

1. Why concurrency control in the database?

A: Databases share resources. Generally, many transactions are running at the same time.

When multiple transactions concurrently access the database, the same data will be read and/or modified at the same time. If concurrent operations are not controlled, incorrect data may be accessed and stored, compromising Database Consistency. Therefore, the database management system must provide a concurrency control mechanism.

2. Which types of data may be inconsistent during concurrent operations? How can we avoid inconsistencies?

A: The data inconsistency caused by concurrent operations includes three types: Loss and modification, non-repeated read, and read "dirty" data.

(1) lost update)

When two transactions t1 and t2 read the same data and modify the data, the results submitted by T2 destroy (overwrite) the results submitted by T1, resulting in the loss of modifications to T1.

(2) Non-repeatableread)

Non-repeated read means that after transaction T1 reads data, transaction T2 performs an update operation, so that T1 cannot reproduce the previous read results.

(3) read "dirty" data (dirtyread)

Reading "dirty" data means that transaction T1 modifies a data and writes it back to the disk. After transaction T2 reads the same data, transaction T1 is revoked for some reason, at this time, T1 has modified the original data recovery value. The data read by T2 is inconsistent with the data in the database, and the data read by T2 is "dirty", that is, the data is incorrect.

　The method and technology to avoid inconsistency is concurrency control.. The most common concurrency control technology is blocking technology.

You can also use other technologies. For example, you can use the timestamp method in a distributed database system to control concurrency.

3. What is blocking?

A: blocking means that transaction t sends a request to the system before performing operations on a data object, such as a table or record, to lock it. After the lock, transaction T has some control over the data object. Before transaction t releases its lock, other transactions cannot update the data object.

Blocking is a very important technology for implementing concurrency control.

3. What are the basic blocking types? Describe their meaning.

A: There are two basic lock types: Exclusive Locks and shared Locks ).

Exclusive locks are also called write locks. If transaction T adds an X lock to Data Object a, only T is allowed to read and modify A. No other transaction can add any type of lock to, wait until T releases the lock on. This ensures that other transactions cannot read or modify A before T releases the lock on.

A shared lock is also called a read lock. If transaction T adds the S lock to Data Object A, transaction T can read A but cannot modify A. Other transactions can only add the S lock to transaction A instead of the X lock, until T releases the S lock on. This ensures that other transactions can read A, but cannot modify A before T releases the S lock on.

5. What is the blocking protocol? What are the main differences between different levels of blocking protocols?

A: When using the blocking technology to lock data, some rules should be agreed. For example, when using the X lock and S lock to lock data objects, you must specify when to apply for the X lock or S lock and when to release the lock. These conventions or rules are called LockingProtocol ). Different blocking protocols are formed when different rules are configured for the blocking method. Different levels of blocking protocols, such as the three-level blocking protocols introduced in "Introduction", the main difference between the three-level protocols is what operations need to be applied for blocking, when to apply for a lock and when to release the lock (that is, the length of the lock holding time ).

Level-1 blocking Protocol: Transaction T must apply an X lock to the data R before it modifies it until the transaction ends.

Level-2 blocking Protocol: The level-1 blocking protocol and transaction T must apply the S lock to the data before reading the data R. After reading the data, the S lock can be released.

Level-3 blocking Protocol: The level-1 blocking protocol and transaction T must be locked by S before reading data R until the transaction ends.

6. What is the relationship between different blocking protocols and the system consistency level?

A: different blocking protocols correspond to different consistency levels.

The level-1 blocking protocol Prevents Loss of modifications and ensures that the transaction T is recoverable. In the level-1 blocking protocol, the S lock is not applied to read data, so it cannot guarantee Repeatable read and non-read "dirty" data.

In addition to preventing loss of modifications, the level-2 blocking protocol can further prevent reading of "dirty" data. In the level-2 blocking protocol, the S lock is released immediately after data is read, so it cannot be rewritable.

In the three-level blocking Protocol, both the Read and Write Data lock is extended, that is, the lock is released until the end of the transaction. Therefore, the third-level blocking protocol not only prevents loss of modifications and does not read "dirty" data, but also further prevents repeated reads.

7. Explain the cause and solution of the active lock.

A: Why live locks are generated: when a series of locks cannot be executed in sequence, some transactions may wait for a lock indefinitely, resulting in a live lock.

A simple way to avoid a live lock is to adopt a first-come-first-served policy. When multiple transaction requests block the same data object, the blocking sub-system queues transactions in the order of request blocking, once the lock on the data object is released, the first transaction in the application queue is authorized to obtain the lock.

8. provide several methods to prevent deadlocks.

A: In the database, the deadlock occurs because two or more transactions have blocked some data objects and all requests have been locked by other transactions, in this case, an infinite wait occurs.

To prevent deadlocks, it is necessary to damage the conditions for creating deadlocks. There are two methods to prevent deadlocks:

(1) One blocking method

Each transaction is required to lock all the data to be used at a time, otherwise the execution cannot continue.

(2) sequential Blocking

Define a blocking order for Data Objects in advance, and all transactions are blocked in this order.

However, the deadlock prevention policy is not suitable for database systems.

9. Please provide a method to detect deadlocks. How can I remove deadlocks when deadlocks occur?

A: The database system generally uses the method that allows deadlocks to occur. The DBMS can remove the deadlock after detecting the deadlock.

The method for diagnosing deadlocks in DBMS is similar to that in the operating system. The timeout method or transaction wait method is generally used.

The timeout method is: if the wait time of a transaction exceeds the specified time limit, a deadlock occurs. The timeout method is easy to implement, but it is possible to misjudge the deadlock. If the transaction waits for a long time beyond the time limit for other reasons, the system will mistakenly think that a deadlock has occurred. If the time limit is set too long, the deadlock cannot be detected in time.

After the DBMS concurrency control subsystem detects a deadlock, it should try to release it. The usual method is to select a transaction with the minimum deadlock cost, undo it, release all the locks held by the transaction, so that other transactions can continue to run. Of course, the data modification operations performed on the canceled firm must be restored.

10. What kind of concurrent scheduling is correct?

A: Serializable scheduling is correct.

Definition of serializable scheduling: the concurrent execution of multiple transactions is correct, and the results are the same only when the results are executed serially in a certain order, we call this scheduling policy serialized.

11. Try to describe the concept of the Two-segment lock protocol.

A: The two-segment lock protocol means that all transactions must lock and unlock data items in two phases.

· Before performing read and write operations on any data, you must apply for and obtain the data blocking function;

· After a blockout is released, the transaction no longer applies for and receives any other blockout.

The meaning of "Two segments" is that transactions are divided into two phases:

The first stage is to obtain the block, also known as the expansion stage. At this stage, the transaction can apply for any type of lock on any data item, but cannot release any lock.

The second stage is the release block, also known as the contraction phase. At this stage, the transaction releases the obtained lock, but no more locks can be applied.

13. Why should I introduce the intention lock? What is the meaning of the intention lock?

A: Intention locks are introduced to improve the efficiency of sub-system blocking. The blocking sub-system supports multiple blocking granularities.

The reason is: in the Multi-granularity blocking method, a data object may be locked in two ways-explicit blocking and implicit blocking. Therefore, when locking a data object, the system should not only check whether there is a (explicit or implicit) blocking conflict with the data object, but also check all its upper-level nodes and all lower-level nodes, check whether the applied blocking conflicts with the (explicit and implicit) blocking on these nodes. Obviously, this check method is very inefficient. Therefore, the intention lock is introduced.

Intention lock means that when locking any node, the intention lock must be applied to its upper node first.

For example, if transaction T needs to apply the X lock to a tuples, it must first apply the IX lock to the relationship and database. In other words, an IX lock is applied to the relationship and database, indicating that its descendant node-A tuples (intention) are subject to an X lock.

After the intention lock is introduced, when the system locks a data object, it does not have to check the blocking conflicts with the next-level node one by one. For example, when transaction T needs to add an X lock to the relational R, the system only needs to check whether the root node database and R itself have applied an incompatible lock (if it finds that IX has been added, conflicts with X), and does not need to search or check whether the X lock or S lock is applied to every tuples in R.

14. Try to describe common intention locks: IS locks, IX locks, and SIX locks, and give the compatibility matrix of these locks.

Answer: IS lock

If an IS lock IS applied to a data object, the S lock IS applied to its descendant node. For example, to apply the S lock to a tuples, you must first apply the IS lock to the relationship and database.

IX lock

If an IX lock is applied to a data object, the X lock is applied to the child node of the object. For example, to apply the X lock to a tuples, you must first apply the IX lock to the relationship and database.

SIX lock

If a SIX lock is applied to a data object, the S lock is applied to it, and the IX lock is added, that is, SIX = S + IX.

Compatibility Matrix (omitted)