[Transaction Management] Two blocking protocols and conflicts can be serialized

Document directory

• How to prove that the results of transaction scheduling that follows the two lock protocols are serializable

How to prove that the results of transaction scheduling that follows the two lock protocols are serializable

How can we prove that the transaction scheduling result following the two lock protocols is serializable?
Example
------ Solution --------------------------------------------------------
9.4. serializable isolation level
Serializable provides the highest level of transaction isolation. This level simulates serial transaction execution, as if the transaction will be serialized one by one, rather than parallel execution. However, applications at this level must be prepared to re-initiate transactions when serialization fails.
When a transaction is serializable, a SELECT query can only view the data committed before the transaction starts, but never see uncommitted data or modifications committed by other parallel transactions during transaction execution. (However, the SELECT statement does show the effect of the previous update in the same transaction. even if the transaction has not been committed .) this behavior is not the same as the Read committed level. The Read committed sees the snapshot at the beginning of the transaction, rather than the snapshot at the beginning of the current query in the transaction.
If a target row returned by a query that is executing an update statement (or delete or select for update) is updated by another parallel uncommitted transaction, the second transaction that attempts to update this row will wait for the commit or rollback of another transaction. If a rollback occurs, you can continue to modify the transaction in the waiting state. If a parallel transaction is committed, a serializable transaction is rolled back and the following information is returned.
Error: Can't serialize access due to concurrent update
Because a serializable transaction cannot change the rows that have been changed by other transactions after the serializable transaction starts.
When the application receives such an error message, it should exit the current transaction and re-execute the entire transaction from the beginning. during the second run, the changes that were committed before the transaction were a part of the database's initial appearance. Therefore, there is no logical conflict between the new version of rows as the starting point of the new transaction update. please note that only update transactions need to be retried-read-only transactions have never had a serialized conflict.
Serializable transaction level provides a strict guarantee: each transaction can see a complete database view. however, if parallel updates make the database unable to maintain serial execution, the application must be prepared to retry the transaction, and the overhead of redoing complex transactions may be very considerable. therefore, we only recommend that you include complex logic in the update query and use it when reading the results that may cause errors at the submitted level.

 

It is proved that if a concurrent transaction complies with the two-segment lock protocol, the concurrent scheduling of these transactions can be serialized.

-Solution -------------------------------------------------------------
Proof: taking two concurrent transactions TL and T2 as examples, there are multiple concurrent transactions, and so on. According to the serializable definition, transaction serializability can only occur in the following two situations:
(L) Transaction TL writes a Data Object A, T2 reads or writes;
(2) The transaction TL reads or writes a Data Object A and T2 writes.
A is a potential conflict object.
Set the common objects with potential conflicts between TL and T2 access to {A1, A2... , }. Without losing its universality, Suppose X = (A 1, A2 ,... , AI} all comply with situation 1. Y = {A I + 1 ,... , An} meets the situation (2 ).
Xlockx (1) is required for VX, X, and TL.
T2 requires slockx or xlockx ②
1) if operation ① is executed first, Tl gets the lock and T2 waits
Due to the two lock protocols, Tl releases the lock only after successfully obtaining the lock for all objects and non-potentially conflicting objects in X and Y.
In this case, if the locks of W, X, and Y are obtained by T2, a deadlock occurs. Otherwise, TL can be executed only after all the objects in X and Y are processed. This is equivalent to sequential execution by Tl and T2. According to the serializable definition, scheduling by Tl and several is serializable.
2) operation ② the first execution is symmetric with (L). Therefore, if a concurrent transaction complies with the two lock protocols, the concurrent scheduling of these transactions must be serializable.

------ Solution --------------------------------------------------------
The result of the Transaction Scheduling processing of the two lock protocols is a sufficient condition for serializability.
However, serializability does not necessarily follow the two-segment lock protocol.
The two lock protocols are as follows,
Transaction R1 R2 r3
R (R1), R (R2), R (r3), w (r3), w (R2), w (R1)
R indicates read. W indicates write indicates operation.
The concept is that the lock cannot appear after the operation.
Since there will be no locks, there will be no confusion ~