MySQL事务处理及锁定_MySQL

bitsCN.com

MySQL事务处理及锁定

事务处理（Transaction）是将多个更新命令作为一个整体来执行，从而保证数据整合性的机制。

存储引擎（Storage Engine）

MySQL 有一个重要特征，即：可替换存储引擎构架（Pluggable Storage Engine Architecture）

MySQL 的功能分为 2 个部分，

外层部分：负责完成与客户端的连接、对 SQL 语句进行事前检查等。

内层部分：存储引擎部分，负责接收外层的数据操作指示，完成实际的数据输入输出以及文件操作。

MySQL 提供了多种存储引擎，用户可*选择（也可以给不同的表，选择不同的存储引擎）

用户可以任意选择存储引擎，这是 MySQL 独有的特征。

MyISAM ：默认的高速引擎，不支持事务处理

InnoDB ：支持行锁定，以及事务处理，比 MyISAM 的处理速度稍慢

ISAM ：MyISAM 引擎的前身。MySQL 5.0 以后，不再标准安装

MERGE ： 该引擎，会将多个 MyISAM 类型的表，作为一个表来处理

MEMORY， HEAP ： 只在内存上保存数据

Falcon ：一种新的存储引擎，支持事务处理

ARCHIVE： 将数据压缩后保存 （只能进行 INSERT / SELECT 操作）

CSV ： 以CSV 形式保存数据（应用于跨平台数据交换）

my.ini 中， 设置：default-storage-engine=INNODB

才能使用 InnoDB 引擎，来支持事务处理

[sql]

SHOW CREATE TABLE customer;

SHOW CREATE TABLE customer/G

[sql]

ALTER TABLE customer ENGINE=MyISAM;

为什么需要事务处理？

例如：甲，向乙转账1 万元。

需要2个操作，甲的账户减少 1万，乙的账户增加 1万。

如果，甲账户减少 1万之后，乙账户却没有相应增加1万，则发生错误。

为避免此类情况，将2个操作作为一个事务来处理，2个操作都成功，则事务结束，进行提交（COMMIT）。

如果 其中1个操作失败，则强制返回最初的状态，即：回滚（ROLLBACK）

这样，能保证，要么2个操作都成功，要么2个操作都失败。

执行ROLLBACK 等事务操作，要确保当前的数据库引擎是支持事务操作的。如：InnoDB

[sql]

BEGIN; -- 或者 START TRANSACTION

DELETE FROM customer;

SELECT * FROM customer;

ROLLBACK;

自动提交的功能，默认是开启的。

[sql]

SELECT @@AUTOCOMMIT;

SET AUTOCOMMIT=0; --关闭自动提交功能

SELECT * FROM customer;

DELETE FROM customer;

ROLLBACK; -- 关闭自动提交，无需 BEGIN 操作，也能使用 ROLLBACK

/* 这种情况下，如果不执行 COMMIT 操作，则所有更新不会反映到数据库中 */

部分回滚

[sql]

BEGIN;

INSERT INTO customer VALUES('T0001', '王二', '1980-10-21', 1);

SAVEPOINT sp;

INSERT INTO customer VALUES('T0002', '田三', '1985-10-21', 1);

SELECT * FROM customer;

ROLLBACK TO SAVEPOINT sp;

SELECT * FROM customer;

ROLLBACK;

SELECT * FROM customer;

以下 SQL 命令，执行后会被自动提交。它们不在事务处理的控制范围之内。

[sql]

DROP DATABASE;

DROP TABLE;

DROP;

ALTER TABLE;

多用户同时读取数据库时候，为防止冲突，可使用锁定（Lock）、解锁（Unlock）

锁定分为：共享锁定（Shared Lock）、排他锁定（eXclusive Lock）

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

排他锁定，也称为，写入锁定 或 独占锁定。

在执行 INSERT、UPDATE、DELETE 等操作时，使用该锁定。其他进程、事务，一律不能读取、更新、写入。

锁定粒度：记录（行）、表、数据库。

锁定提升机制：当行单位粒度的锁定，大量产生时，数据库将锁定粒度自动向上提升（比如，提升成表粒度的锁定，或者数据库粒度的锁定）

分离水平：事务处理之间的影响程度（同时运行时互相影响的机制）

分离水平有 4 种：

READ UNCOMMITTED ：支持， 非提交读取、不可重复读取、幻象读取

READ COMMITTED ：支持，不可重复读取、幻象读取

REPEATABLE READ ：支持，幻象读取

SERIALIZABLE ：都不支持

非提交读取（脏读 Dirty Read）：能从别的事务处理中，读取到尚未提交的更新数据，只发生在 READ UNCOMMITTED 分离水平。不推荐使用

[sql]

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

不可重复读取（Non-Repeatable Read）：某一事务处理中，由于其他事务处理的更新操作，导致，对同一数据的多次读取，结果不同。

发生在 READ COMMITED 以下的分离水平

幻象读取（Phantom Read）：某一事务处理中，对同一数据进行多次读取时，由于其他事务处理的插入、删除操作，

使得结果中，出现了第一读取时，不存在的数据，或者，第一次读取时，存在的数据消失了，的现象。

死锁（Dead Lock）两个不同的事务处理，在相互等待对方释放锁定，永远也不可能解除锁定的一种状态。

事务处理的机制，简单的说，就是，留下更新日志。

与事务处理相关的日志，分为2个类型：UNDO 日志、 REDO 日志

UNDO 日志，又称：回滚段（Rollback Segment）

REDO 日志，又称：事务处理日志、或 日志。

bitsCN.com