【数据库基础】事务控制（Transaction）Part 1

围巾师傅

已于 2022-06-10 11:07:21 修改

阅读量857

点赞数 3

分类专栏：数据库基础文章标签：数据库 database

于 2022-06-04 22:37:05 首次发布

本文链接：https://blog.csdn.net/YFY20020109/article/details/125119971

版权

数据库基础专栏收录该内容

5 篇文章 4 订阅

订阅专栏

事务控制Part 2：级联回滚，死锁，时间戳，日志文件，检查点等https://blog.csdn.net/YFY20020109/article/details/125133798?spm=1001.2014.3001.5502

一、事务的定义、性质

what is Transaction：

4 Properties:

二、并发问题（Concurrency Problem）

1.丢失更新问题（Lost updates problem）

2.未提交依赖问题 / 脏读问题（Uncommitted dependency / Dirty read problem）

3.异常分析问题（Incorrect analysis problem）

三、并发控制、调度（Concurrency Control & Schedule）

四、上锁（Locking）

两相锁（Two-Phases Locking，2PL）

五、可串行性（Serialisability）

一、事务的定义、性质

what is Transaction：

Action, or series of actions, carried out by user or application, which reads or updates(write) contents of database.

Two outcomes:

• Success (commit)

• Failure (rollback)

An application program can be thought as a series of transactions.

Transforms database from one consistent state to another, although consistency may be violated during transaction.

Eg.

4 Properties:

Atomicity：‘All or nothing’ property，a transaction is performed in its entirety or is not performed at all；The logic unit of work

原子性：事务像原子一样不可再分

Consistency：Must transform database from one valid state to another.

Isolation：Partial effects of incomplete transactions should not be visible to other transactions.

transactions execute independently of one another

After a failure, an uncommitted transaction is rolled back and all of its effects are erased

A transaction updates a tuple, but has not committed the changes，another transaction cannot read the uncommitted data

如果某一事务重写了X后发生撤回（rollback），以后其他事务读取的X应仍是重写前的X

Durability：Effects of a committed transaction are permanent and must not be lost

because of later failure；保存了的事儿，那就是永久保存了

二、并发问题（Concurrency Problem）

In order to run transactions concurrently we interleave their operations，

Each transaction gets a share of the computing time，

This leads to 3 main problems：

• Lost updates problem

• Uncommitted dependency problem

• Incorrect analysis problem

All arise because isolation is broken.

三种问题本质上相同，根源都在事务之间互相造成不正当的干涉，只不过表现的结果不一样：

1.丢失更新问题（Lost updates problem）

一个事务改写了另一个事务已经上传的结果

2.未提交依赖问题 / 脏读问题（Uncommitted dependency / Dirty read problem）

一个事务读入别的事务正在加工的数据项；

“Rollback Problem”：它造成的结果多半是T1已经rollback，可T2还在用已经废止的数据值处理问题，最终结果也不正确

3.异常分析问题（Incorrect analysis problem）

不像前两种problem，问题大概率出在被读取再改写的值X、Y本身；而是出在由他们推导产生的新数据出错（如加和sum等），原因是X、Y等值的改写与得出新数据的值两种操作顺序出现了错位（如用原来的X算出了sum后，才把新X成功上传）。

Eg.

三、并发控制、调度（Concurrency Control & Schedule）

Concurrency control : process of managing simultaneous（同时的） operations on the database without having them interfere（干扰） with one another

让不同事务各自独立

schedule： a sequence of the operations by a set of concurrent transactions that preserves the order of the operations in each of the individual transactions
serial schedule（串行调度）： a schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions

各个事务串起来，一个接一个，干完这一件才会干下一件。不会在某时停下正在做的事务去做另一件事，各个事务不会穿插进行

四、上锁（Locking）

Locking is a conservative approaches for concurrency control - delay transactions in case they conflict with other transactions
Transaction uses locks to deny access to other transactions and so prevent incorrect updates
分为两种：

共享锁（又叫读锁） Shared lock（read lock）：和其他事务共同使用数据

互斥锁（写锁） Exclusive lock（write lock）：阻拦其他事务使用该数据

Before reading from a resource a transaction must acquire a read-lock

Before writing to a resource a transaction must acquire a write-lock

A transaction may not acquire a lock on any resource that is write-locked by another transaction

数据源只能同时上1个互斥锁，一个事务上了，其他的就暂时上不了

A transaction may not acquire a write-lock on a resource that is locked by another transaction

不管那个数据源现在身上有什么锁，只要有，就不能再上互斥锁

上了read-lock，只能对它再上read-lock而不是write-lock；而上了write-lock，就什么也不能再上了

If the requested lock is not available, transaction waits

可是，加锁仍可能发生并发问题。还需要正确的加法：

两相锁（Two-Phases Locking，2PL）

All locking operations precede unlock operation in the transaction

每个要锁的数据开头先全锁上，最后再全解开，所有上锁的操作必在所有解锁操作之前即可。

Every transaction must lock an item (read or write) before accessing it 先锁再读

Once a lock has been released, no new items can be locked

what is Two phases？

1. Growing phase - acquires all locks but cannot release any locks.

2. Shrinking(收缩) phase - releases locks but cannot acquire any new locks.

2PL解决三大并发问题：

1.Lost Update Problem

Preventing the Lost Update Problem using 2PL

注意：虽然最终T1还是改变了T2改写过的X，但它和 lost update不一样。这次T1不仅上传，而且读入的都是T2处理完的X，这说明T1本就需要T2先处理后的X——T1依赖于T2！

2.Uncommitted Dependency Problem

3.Inconsistent Analysis Problem

90+50+35 = 185 ？！

五、可串行性（Serialisability）

Nonserial Schedule: Schedule where operations from set of concurrent transactions are interleaved. 可参考serial schedule，它的对立面；各个事务你中有我我中有你
Objective of serialisability is to find nonserial schedules that are equivalent to some serial schedule. Such a schedule is called serialisable.

如：某调度A包含事务T1、T2，T2全过程夹在T1前半段和后半段之间，之类的，如果调度A实际效果=调度B（先T1 |||| 后T2），或者调度A=调度C（先T2 |||| 后T1），那么A就可以被串行化

A schedule is conflict serialisable（冲突可串行） if transactions in the schedule have a conflict but the schedule is still serialisable.

Two transactions have a conflict If at least one is a write and they use the same resource.

Details：

Transaction T1 reads data items A, B and C, a concurrent transaction T2 reads data items B, C, and D, and write data item D. Then T1 and T2 have NO conflict！

尽管都作用于B、C，但只有D被写了

The timing locks released is important for serialisable schedules
Two-phase locking (2PL) can guarantee serialisability

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