【数据库基础】事务控制（Transaction）Part 2：级联回滚，死锁，时间戳，日志文件，检查点

(32条消息) 【数据库基础】事务控制（Transaction）Part 1_围巾师傅的博客-CSDN博客https://blog.csdn.net/YFY20020109/article/details/125119971

---

目录

一、锁带来的问题

（一）级联回滚（Cascading Rollback）

（二）死锁（Deadlock）

Timeout（超时）

 死锁预防（Deadlock Prevention）

Conservative 2PL

Rigorous 2PL and Conservative 2PL are NOT the same thing

二、时间戳（Timestamping）

三、最优并发控制（Optimistic Concurrency Control，OCC）

四、数据的粒度（Granularity of Data Item）

五、数据恢复（Data Recovery）

（一）日志文件（Log File）

（二）检查点（checkpoint），重做（redo），撤销（undo）

Summary

---

一、锁带来的问题

即使是两相锁（2PL），使用不严格也会导致这些问题：

（一）级联回滚（Cascading Rollback）

Definition：

A transaction (T1) causes a failure and a rollback must be performed. Other transactions dependent on T1's actions must also be rollbacked, thus causing a cascading effect.

One transaction's failure causes many to fail.

Cascading rollbacks are undesirable since they potentially lead to the undoing of a significant

amount of work.

Solution: 

Postpone the release of all locks until end of the transaction（Rigorous 2PL）.

 严格执行2PL的示例：

---

（二）死锁（Deadlock）

Definition：2 (or more) transactions are each waiting for locks held by the other to be released

这是个“两只小羊过独木桥”的伤心故事。。。当两个事务都在等待对方解锁时，它们再也不能做任何事，除非其中某个事务突然终止，否则它们等会永远等下去

Solution：only one way to break deadlock—— abort one or more of the transactions

General techniques：

Timeout（超时）

不论死锁是否出现都有可能触发它

Def：Transaction that requests lock will only wait for a system-defined period of time.

If lock has not been granted within this period, lock request times out.

It aborts and automatically restarts the transaction.

*When using timeouts, it is possible that the DBMS aborts a transaction that is not in a deadlock.

 死锁预防（Deadlock Prevention）

杜绝死锁发生的可能

Def：DBMS looks ahead to see if the transaction would cause a deadlock and never allows a deadlock to occur.

Conservative 2PL

Def：All data items have to be locked at the beginning of a transaction.

If all locks are acquired at the beginning of the transaction, then deadlock will not happen.

Rigorous 2PL and Conservative 2PL are NOT the same thing

Rigorous：keep all locks until the end

Conservative：acquire all locks at the beginning

a safest 2PL = Rigorous + Conservative

---

二、时间戳（Timestamping）

A concurrency control mechanism.

• Transactions are ordered globally so that older transactions (i.e. with smaller timestamps) get priority in the event of conflict.
• Read/write proceeds only if last update on that data item was carried out by an older transaction.
• Otherwise, transaction requesting read/write is restarted and given a new timestamp.
• No locks so no deadlock.
• There are also timestamps for data items:       

        • read-timestamp - timestamp of last transaction to read item;

        • write-timestamp - timestamp of last transaction to write item

• When transaction T asks to read/write a data item x, database will compare T’s timestamp with the read/write timestamp of x, then decide whether to proceed T or abort/restart T.

基本原理如下：

---

三、最优并发控制（Optimistic Concurrency Control，OCC）

• Assumption: conflict is rare → more efficient to let transactions proceed without delays to ensure serialisability. （e.g., All transactions are readers）
• At commit, a check is made to determine whether conflict has occurred.

            If there is a conflict, transaction must be rolled back and restarted .

• Potentially allows greater concurrency than traditional protocols.

Phase:

1. Read:

     i. reads the values of all data items it needs from the database and stores them in local               variables.

     ii. Writes in the local variables.

2. Validation:

      ensure there are no conflicts

3. Write:

    （ if validation was successful) the updates in the local variable are applied to the public                  database

---

四、数据的粒度（Granularity of Data Item）

Definition：Size of data items chosen as unit of protection by concurrency control protocol.

Ranging from coarse to fine:

    • The entire database.

    • A file.

    • A page (or area or database space).

    • A record.

    • A field value of a record.

Tradeoff:

    • coarser, the lower the degree of concurrency;

    • finer, more locking information that is needed to be stored.

Best item size depends on the types of transactions.

五、数据恢复（Data Recovery）

There are several facilities：

• Backup mechanism：makes periodic backup copies of database.

• Logging facilities：keep track of current state of transactions and database changes.

• Checkpoint facility：enables updates to database in progress to be made permanent.

• Recovery manager：allows DBMS to restore database to consistent state following a failure.

（一）日志文件（Log File）

Definition： Contains information about all updates to database:

     • Transaction records.

     • Checkpoint records.

Often used for other purposes (e.g. auditing).

Transaction records contain:

     • Transaction identifier.

     • Type of log record, (transaction start, insert, update, delete, abort, commit).

     • Identifier of data item affected by database action (insert, delete, and update operations).

     • Before-imag of data item.

     • After-image of data item.

     • Log management information

E.g.

---

（二）检查点（checkpoint），重做（redo），撤销（undo）

Definitions：

• Point of synchronization between database and log file. All buffers are force-written to secondary storage.
• Checkpoint record is created containing identifiers of all active
  transactions.
• When failure occurs：
  leave transactions committed before the checkpoint alone，they have done their work；
  redo all transactions that committed since the checkpoint ，即前滚（rollforward）；

     （because they are unfinished when we check，after checking，if a crash happen at some time，we’re not sure if the transactions committed before crash，so it's safest to do them again）

       undo all transactions active at time of crash ，即回滚（rollback）；

E.g. A number of transactions being processed from t0 until tf when system had a power failure. At tc a checkpoint was done. Explain for each transactions in the figure below how recovery is done.

---

Summary

注：部分内容参考自图书《数据库系统-设计、实现与管理（基础篇）》