SQL事务处理及锁定

最新推荐文章于 2024-04-13 22:07:33 发布

士416

最新推荐文章于 2024-04-13 22:07:33 发布

阅读量410

点赞数

分类专栏： sql开发

本文链接：https://blog.csdn.net/weixin_39441762/article/details/102381708

版权

sql开发专栏收录该内容

7 篇文章 0 订阅

订阅专栏

事务处理(Transaction)是将多个更新命令作为一个整体来执行，从而保证数据整合性的机制。与锁定机制以分离概念结合，可以看作保持数据信赖性的同时，维持数据库的性能的方法。事务处理是在实际的数据库运用中必不可少的功能。

1.存储引擎(Storage Engine)

MySQL功能可以分为两个部分，外层部分主要完成与客户端的连接以及事前调查SQL语句的内容功能，而内层部分就是存储引擎部分，它负责接收外层的数据操作指示，完成实际的数据输入输出以及文件操作工作。

设置存储引擎：在MySQL中，要使用事务处理功能时，必须将表设置为使用InnoDB引擎。使用[SHOW CREATE TABLE]命令，可以查看表中使用的存储引擎。在[ENGINE=]后显示的部分就是正使用中的引擎。

存储引擎的变更：表中使用的引擎可以在定义后进行变更。变更使用[ALTER TABLE]命令，具体语法如下：

ALTER TABLE table_name ENGINE=新引擎;

当监视器中显示了[Query OK]的结果后，说明存储引擎的变更成功。最后在使用[SHOW CREATE TABLE]命令确认更改结果。

2.事务处理

事务处理：将多个事件作为同一个事务来处理，只有多个事件处理都成功的情况下，整个事务才结束，并提交COMMIT；若存在其中一件事出现错误，强制返回导最初状态（这种状态在数据库中称之为回滚）。这样的好处是，避免事件A发生成功后，但是事件B未成功，导致数据库中出现混乱。例如：现金转账。甲从自己的银行账户中向乙转1w元。这包含两个事件，甲从自己的银行账户中扣除1w元和向乙的账户存进1w元。如果这两个事件只成功发生了一件，假设甲完成从自己的账户扣除1w元，而向乙的账户存进1w元未成功。就会造成甲的账户减少了1w元，而乙的账户未增加1w元，消失了1w元。这种情况下，采用事务处理，就不会发生该问题了。

事务处理功能：使用事务处理功能时，涉及3个重要的命令BEGIN、COMMIT和ROLLBACK，它们的语法分别如下：

声明事务处理开始	BEGIN或START TRANSACTION
提交整个事务	COMMIT
回滚到事务开始的状态	ROLLBACK

实例：在事务处理中删除表customer的全部数据，然后用ROLLBACK命令看是否能恢复到事务开始前的初始状态，具体步骤如下：

step1 : 首先将表customer的存储引擎修改为[InnoDB]。

step2：确认表customer中的数据。

step3：事务开始。

step4：删除表customer中的全部数据。

step5：再次确认表customer中的数据。

step6：回滚处理。

step7：确认表的customer中的数据是否恢复。

自动提交功能：指数据库中的操作，未进行事务处理，只要执行了命令就被提交的行为，称之为自动提交功能。

部分回滚：只提交针对数据库的部分操作。在事务处理过程中定义保存点(SAVEPOINT)，然后回滚到指定的保存点前的状态。定义保存点，以及回滚到指定保存点前状态的语法如下：

定义保存点	SAVEPOINT保存点名
回滚指定的保存点	ROLLBACK TO SAVEPOINT保存点名

实例：

% MySQL
BEGIN;
INSERT INTO customer VALUES ('T0001','王二','1980-10-21','1');
INSERT INTO customer VALUES ('T0002','天二','1977-08-31','1');
SAVEPOINT sp;
INSERT INTO cusotmer VLUES ('T0003','星儿','1995-02-11','2');
SELECT * FROM cusotmer;
ROLLBACK TO SAVEPOINT sp; % 此操作将会显示sp前的数据

事务处理的利用范围：部分SQL命令，执行后将被自动提交，是在事务处理可以利用的范围之外。

DROP DATABASE

DROP TABLE

DROP

ALTER TABLE

3.锁定与事务处理分离水平

锁定：是为了是数据库中的特定的数据不然其他用户操作而上的一把锁的机制。解除锁定时被称为解锁。

锁定的种类：按照使用的目的可以将锁定分为共享锁定(Shared Lock)与排他锁定(eXclusive Lock)。

共享锁定是当用户参照数据时，将对象数据变为只读形式的锁定，也被称之为读取锁定。

排他锁定是使用INSERT/UPDATE/DELETE命令对数据进行更新时使用的锁定。其他进程一律不能对读取该数据。实施了排他锁定的数据，在其他食物处理中当然不能进行更新以及参照。因此也被称之为写入或独占锁定。

锁定粒度：锁定对象的大小，单位通常被称为锁定的粒度。支持的粒度随着数据库的不同而有所差异，有以下3种锁定的粒度：

记录（行）、表、数据库。

锁定的粒度影响同时运行的进程数量（或称作同时运行性）。例如，在实施了行锁定的情况下，还可以对同一表的其他行进行处理，而如果实施了表锁定，那么其他的事务处理只能在这个表锁定被解除后才能进行操作。一般情况下，锁定的粒度越小同时运行性才越高。但锁定的数目越多，消耗的服务器资源也会越多。

这里，如果数据库种行单位粒度的锁定大量发生的情况时，数据库有将这些锁定的粒度自动向上提升的机制，通常被称为锁定提升(Lock Escalation)。

多用户数据更新中理解事务处理的分离水平

分离水平：同时运行时相互影响的机制。分离水平越高，数据的整合性越高，但同时运行性下降。相反如果分离水平越低，数据整合性降低的同时，运行性提高了。根据数据的用途，分离水平的选择是开发人员必须判断或决定的。

事务处理分离水平
分离水平	非提交读取	不可重复读取	幻象读取
READ UNCOMMITTED	YES	YES	YES
READ COMMITTED	NO	YES	YES
REPEATEBLE READ	NO	NO	YES
SERIALIZABLE	NO	NO	NO

（1）非提交读取

非提交读取又称为脏读（Dirty Read）。即能从别的事务中读取到还没有提交的更新数据。非提交读取的现象只发生于分离水平为READ UNCOMMITED的场合。READ UNCOMMITED水平是对其他从事务处理中的读取动作没有进行任何限制的分离水平，通常不推荐使用。

SET TRANSCATION ISOLATION LEVEL适用于修改分离水平的命令。制定了SESSION关键字后，，设定只适用于当前的连接（如果指定了GLOBAL关键字，则使用于其后其他新连接）。

（2）不可重复读取

不可重复读取(Non-Repeatable Read)是在某一事务处理中对同一数据进行多次读取，但由于其他事务处理的更新动作读取的数据状态发生了改变。

（3）幻象读取

幻象读取（Phantom Read）是在某一事务处理中对同意数据进行多次读取时，但由于其他事务处理中进行了记录的插入/删除动作产生了结果中出现第一次读取时不存在的数据，或者第一次读取时有的数据消失了的现象。这样的数据增加或消失的现象被称为幻象(Phantom)。

死锁(Dead Lock)：所谓死锁是两个不同的事务处理在相互等待对方释放锁定，永远也不可能解除锁定的一种状态。如果检测到死锁，大多数数据库都是采取将一方锁定强解除，并ROLLBACL。这时，被解除方的事务处理返回错误，另一方就可以按原来的操作继续了。

为防止死锁现象的发生，应该尽量对同一对象数据按照相同的顺序进行操作。

4.深度理解事务处理内部的动作

事务处理的机制简单地说就是留下更新日志。数据库会根据这些日志信息，在必要时将旧数据取回，或者在发生错误时将数据恢复到原先的状态。与事务处理相关的日志可以分为两个类型，一个是UNDO日志，另一个是REDO日志。

（1）UNDO日志

UNDO日志又被称为回滚段(Rollback Segment)，在进行数据的插入、更新、删除的场合，保存变更前的数据。

在表的内容保存了指向UNDO日志的指针，ROLLBACK时根据这个指针来获得旧数据，并覆盖新数据。ROLLBACK后，或者COMMIT后UNDO日志将被删除。另外，UNDO日志也是用于保持数据读取的整合性。

（2）REDO日志

REDO日志根据数据库的不同，有时被称为事务处理日志或日志。

事务处理确定后，由于错误等原因使数据的更新没有正确反映到数据库中的时候，REDO日志提供了数据恢复使用的手段。

数据库在进行更新处理时，并非立即进行更新文件，而是首先在被称为缓冲（buffer cash）的内存空间中进行数据更新，并将这些保存到UNDO日志文件中。因此，数据的更新需要经过缓冲部分数据更新，再到数据库的更新。

数据库中向数据文件写入时有延迟。向数据文件的写入动作是在出发称为检查点（check point）时非同步进行的。检查点按一定的周期触发。如果频繁进行复杂的处理，尤其是对硬盘写入处理时将会出现很高的负荷。因此，现在的处理方式是，数据库将对硬盘的写入操作稍微保留片刻，当检查点（check point）到来时再集中处理，这样会减少访问硬盘次数，性能得到改善。

问题出现在REDO日志更新于数据文件的更新之间的延迟上。如果在此延迟之间发生了断电（故障），硬间错误时，内存中的信息消失，只剩下REDO日志中保存了信息，此时REDO日志用于复原信息。这样避免了重新从硬盘上读取数据的操作。