Database-The serializable of concurrent scheduling

The serializable of concurrent scheduling

DBMS different scheduling (schedule) for concurrent transactions may produce different results
What is the correct dispatch?

Serialization (Serial) scheduling is correct
For serial scheduling, the operation of each transaction does not intersect, and there is no interference, and of course, it does not cause concurrency. As mentioned earlier, the role of a transaction on a database is to transform the database from one consistent state to another. After multiple transactions are executed serially, the database remains in a consistent state.

Serializable (Serializable) scheduling
Concurrent execution of multiple transactions is correct when and only if the result is the same as the result when the transactions are executed serially in a sequential order. Serializable scheduling also maintains a consistent state of the database

[例]现在有两个事务，分别包含下列操作：事务T1：读B；A=B+1；写回A事务T2：读A；B=A+1

Serializable (serializability)
Is the criterion for the correct scheduling of concurrent transactions. In RDBMS, as the correctness criterion of concurrency control. A given concurrent dispatch, when and only if it is serializable, is considered to be the correct dispatch

Sufficient conditions for serializable scheduling

A dispatch SC will get another dispatch SC ' if SC ' is serial, called scheduling SC for conflicting serializable, in order to ensure that the order of the conflicting operations is constant, by exchanging two transaction non-conflicting operations
A scheduling is a conflict serializable, it must be serializable scheduling
The general RDBMS will use the conflict serializable as the correctness criterion of concurrency control

Conflict action (Conflict operation)

A conflict operation is a read and write operation of a different transaction to the same data.
Ri (x) with WJ (x)/* Transaction ti read X,TJ write x*/
Wi (x) with WJ (x)/* Transaction ti writes X,TJ write x*/
Other operations are non-conflicting operations
Conflicting actions for different transactions and two operations of the same transaction cannot be exchanged (commute), otherwise it will affect the performance of the execution

[example) there is now dispatch Sc1=r1  (a) W1 (a) Span class= "hljs-built_in" >r2  (a) W2 (a) r1  (b) W1 (b) r2  (b) W2 (b) Exchange of W2 (a) with r1  (b) W1 (b), Received: r1  (a) W1 (a ) r2  (a) r1  (b) W1 (b) W2 (A) r2  (b) W2 (b) re-r2  (A) and r1  (b) W1 (b) Exchange: Sc2=r1  (a) W1 (a) r1  (b) W1 (b) r2  (a) W2 (a) r2  (b) W2 (b) SC2 equivalent to a serial dispatch T1,T2,SC1 conflict serializable schedule  

冲突可串行化调度是可串行化调度的充分条件，不是必要条件。还有不满足冲突可串行化条件的可串行化调度，称为目标可串行化(view serializability)的调度。    [例]有3个事务, L1和L2是目标等价的（view equivalence）       T1=W1(Y)W1(X)，T2=W2(Y)W2(X)，T3=W3(X)调度L1=W1(Y)W1(X)W2(Y)W2(X) W3(X)是一个串行调度。调度L2=W1(Y)W2(Y)W2(X)W1(X)W3(X)不满足冲突可串行化。但是调度L2是可串行化的，因为L2执行的结果与调度L1相同，Y的值都等于T2的值，X的值都等于T3的值 

Blockade protocol

When to apply for a blockade
Lock time
When to release the blockade, etc.
The two-block protocol (two-phase Locking, abbreviated as 2PL) is the most commonly used blocking protocol, which theoretically proves that the use of a two-block blocking protocol produces a serializable dispatch

Two-stage lock protocol
means that all transactions must lock and unlock data items in two stages
Before a read or write operation on any data, the transaction must first obtain a blockade of the data
After releasing a blockade, the transaction no longer applies and obtains any other blockade

Meaning of the "two-paragraph" lock
A transaction is divided into two stages
The first stage is to get the blockade, also known as the expansion phase
A transaction can request any type of lock on any data item, but cannot release any locks
The second stage is to release the blockade, also known as the contraction phase
A transaction can release any type of lock on any data item, but cannot request any more locks

例事务Ti遵守两段锁协议，其封锁序列是 ：Slock A    Slock B    C     Unlock B    Unlock A   C；|←      扩展阶段    →|  |←      收缩阶段           →|事务Tj不遵守两段锁协议，其封锁序列是： Slock A    Unlock A    Slock B    C    C    Unlock B；

Transaction adherence to the two-phase lock protocol is a sufficient condition for serializable scheduling, not a requirement.
If concurrent transactions adhere to the two-phase lock protocol, any concurrent scheduling policies for these transactions are serializable
If a schedule of concurrent transactions is serializable, not all transactions are compliant with the two-phase lock protocol
Two-stage lock protocol and a blocking method to prevent deadlock
A blocking law requires that every transaction must be locked at once for all data to be used, otherwise it cannot continue, so a blocking method adheres to the two-phase lock protocol
However, the two-phase lock protocol does not require the transaction to lock all data to be used at one time, so a transaction that adheres to the two-phase lock protocol can deadlock

Blocking granularity (granularity)

The size of the blocked object is called the blocking granularity (granularity)
Blocked objects: Logical unit, physical unit
Example: In a relational database, block objects:
Logical unit: attribute value, attribute value collection, tuple, relationship, index item, entire index, entire database, etc.
Physical units: Pages (data pages or index pages), physical records, etc.

The granularity of the blockade is closely related to the concurrency of the system and the overhead of concurrency control.
The greater the granularity of the blockade, the fewer data units the database can block, the smaller the concurrency, and the smaller the system overhead;
The smaller the granularity of the blockade, the higher the concurrency, but the greater the system overhead
Cases
If the blocking granularity is a data page, and the transaction T1 needs to modify the tuple L1, T1 must lock the entire data page a containing the L1. If T1 T2 to modify the tuple L2 in a after a lock on a, the T2 is forced to wait until T1 releases a.
If the block granularity is a tuple, then T1 and T2 can lock the L1 and L2 simultaneously, without waiting for each other, which improves the degree of parallelism of the system.
As another example, the transaction T needs to read the entire table, if the block granularity is a tuple, T must lock each tuple in the table, the overhead is great

Multi-granularity blockade (multiple granularity Locking)

在一个系统中同时支持多种封锁粒度供不同的事务选择

Select the size of the blockade
At the same time consider the blocking overhead and concurrency factors, appropriate selection of blocking granularity
User transactions for a large number of tuples that need to handle multiple relationships: Database as a blocking unit
User transactions that need to handle a large number of tuples: with relationships as block units
Handle only a few tuples of user transactions: tuples as block units

Multi-granularity Tree
A tree-shaped structure to represent the granularity of multilevel blockade
The root node is the entire database, representing the largest data granularity
Leaf nodes represent the smallest granularity of data
Allows each node in a multi-granularity tree to be locked independently
Locking a node means that all descendant nodes of the node are also locked in the same type
In a multi-granularity blockade, a data object may be blocked in two ways: explicit blocking and implicit blocking
Explicit blocking: Standalone blocking directly added to data objects
Implicit blocking: The data object does not have an independent lock, because its parent node is locked and the data object is added to the lock
The effect of explicit blocking and implicit blocking is the same!

When the system checks for a blocking conflict
To check for an explicit block
and to check for implicit blocking.
For example, transaction T to R1 the relationship x lock
The system must search its parent node database, relational R1.
Also search for subordinate nodes of R1, that is, each tuple in R1
If one of the data objects has an incompatible lock already added, T must wait for

To lock a data object, the system checks
The data Object
There is no explicit blockade with which the conflict
All ancestor nodes (ancestors)
Check if the explicit blocking of this transaction conflicts with the implicit blocking of other transactions on the data object: (caused by a blockade that has been added by the parent node)
All subordinate nodes (descendants)
See if the explicit blocking of other transactions on the subordinate nodes conflicts with the implicit blockade of this transaction (the blockade that will be added to the subordinate node)

Data sharing and data consistency are a couple of contradictions
The value of a database depends to a large extent on the degree of data sharing it can provide
Data sharing depends to a large extent on the degree to which the system allows concurrent operations on data
The degree of data concurrency depends on the concurrency control mechanism in the database
The consistency of the data also depends on the degree of concurrency control. The more concurrency control is applied, the better the consistency of the data

concurrency control for databases in transaction units
Concurrency control for databases typically uses a blocking mechanism
The two most common types of blocking

The criterion of the concurrency control mechanism to dispatch the correctness of concurrent transaction operation is serializable
The correctness of concurrent operations is usually guaranteed by the two-segment lock protocol.
Two-stage lock protocol is a sufficient condition for serializable scheduling, but not a requirement

Database-The serializable of concurrent scheduling