Database Advanced Application Transactions

The basic composition of a transaction:

Begin Transation
Update Payment Form Set Account Total = Total Account-N
where account name = ' A '
Update Payment Form Set Account Total = Total account + N
where account name = ' B '
Commit

Success is a commit
Failure is rollback

Transaction concurrency problem: Database lock technology:
1. Loss of data modifications (T1 submissions are covered by T2)
2. Read dirty data (data modifications made by T1 are written to the disk, T2 just uses T1 modified data, then T1 recalls the previously used data, causing T2 data to be inconsistent with the original, resulting in dirty reads)
3. Non-repeatable read (duplicate read of the same transaction, inconsistent data found before and after)
4. Generate Phantom data (caused by multiple use of the same data)

Lock technology for the database:

Role:
To address some of the problems that occur with concurrent operations


Exclusive lock (x Lock): After the transaction T1 an X lock on the data object, only T1 can read and modify the data object until it is released

Shared lock (S-lock): After a transaction T1 a s lock on a data object, T1 can only query the data object until it is released.

Blockade protocol:

First level blocking protocol: Add an X lock to the data object you want to modify until the T1 execution is complete.
Secondary block protocol: On the premise of a first-class blockade, the data object is added S lock until the read data is completed
Level three blocking technology: on the premise of a first-level blockade, the data object is added s lock until the transaction is completed

Live Lock: T1 block Data r,t2 also request block R ... Depending on the system's first approval of other transactions, the result is a waiting situation.
Workaround: Adopt A first-come-first service strategy.

Deadlock: T1 blocked the data r1,t2 blocked the data R2, then the data between them blocked each other, and then in a waiting situation
WORKAROUND: 1. Use a blocking method to block all data objects used at once
2. Sequential blocking method: Pre-arranging a used data object for blocking order (not good, when data volume is not available)

Database Advanced Application Transactions