跳至主要內容

MySQL事务

棋大约 19 分钟约 5709 字

事务机制

事务修改的概念

事务是一组相互依赖的操作单元的集合，用来保存对数据库的正确修改，保证数据的完整性，如果事务的某个单元操作失败，将取消本次事务的全部操作。如银行交易，A给B转账，需要执行两次操作：A的银行资金减少，B的银行资金增加，若B没有转账成功，则取消A的本次扣除，即事务回滚到扣除之前，即A中没少，B中没多，事务的特征如下：

  • 原子性：每个事务是一个不可分割的整体，只有所有的操作都执行成功，事务才能够提交，否则此次事务失败，所有成功执行的操作单元都需撤销，数据库回到此次事务之前的状态。

  • 一致性：在执行一次事务后，关系数据的完整性和业务逻辑的一致性不能被破坏。假如A和B转账结束后，资金总额是不能改变的。

  • 隔离性：在并发环境中，一个事务所做的修改必须与其他事务所做的修改相隔离。例如一个事务查看的数据必须是其他并发事务不能查修改之前或修改完毕的数据，不能是修改中的数据。

  • 持久性：事务结束后，对数据的修改是永久保存的，即使数据故障导致数据库系统重启，数据依然是修改后的状态。

事务机制的重要性

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库中只有一个账户表tb_account，保存着两张借记卡账户A和B，并且要求这两张借记卡都不能透支。下面通过具体的代码演示一下没有事务机制的话会有什么后果。

  1. 创建银行数据库bank，在下面创建数据表tb_account，代码如下。

    create database bank;
use bank;
create table tb_account (
id int unsigned not null auto_increment primary key,
    name varchar(50) not null,
    balance float(8,2) unsigned default 0
);
    运行结果
    运行结果

  2. 向银行数据表中插入两条记录，具体代码如下。

    insert into tb_account (name,balance) values
('A',1000),('B',0);
select * from tb_account;
    运行结果
    运行结果

  3. 创建模拟转账的存储过程，代码如下。

    delimiter //
create procedure proc_transfer(in name_from varchar(50),in name_to varchar(50),in money int) reads sql data
begin
update tb_account set balance = balance + money where name = name_to;
update tb_account set balance = balance - money where name = name_from;
end
//
    运行结果
    运行结果

  4. 调用刚刚创建的存储过程，实现A向B转账七百元，代码如下。

    call proc_transfer('A','B',700);
select * from tb_account;
    运行结果
    运行结果

  5. 再次尝试转账700元，代码如下。

    call proc_transfer('A','B',700);
select * from tb_account;
    运行结果
    运行结果

    可以看到，由于A的账户不足700元，所以扣款失败，但是B账户还是收到了700元，这是因为在扣除A账户的700元时发生了异常，我们应该撤销B账户得到的700元，并没有，这并不符合我们的预期，所以事务就可以解决这一问题。在有了事务之后，我们就可以将扣款和转账的语句看成一个整体，若扣款失败，则目标账户的资金不能增加，或者扣除已经增加不该增加的金额。

关闭MySQL自动提交

MySQL默认开启自动提交，也就是说，MySQL会自动地把每一个语句都看成一个事务，在执行完该语句后执行提交操作。

查看MySQL的自动提交是否开启

查看MySQL的自动提交是否关闭有以下几种方法。

使用SHOW VARIABLES语句，语法如下。

SHOW VARIABLES LIKE 'autocommit';
运行结果
运行结果

在Value字段值为ON或1时表示自动提交开启，值为OFF或0时表示自动提交结束。

关闭自动提交有以下两种情况：

  1. 在当前连接中，可以通过将AUTOCOMMIT变量设置为0，来禁用自动提交功能，代码如下。

    set autocommit = 0;
show variables like 'autocommit';
    运行结果
    运行结果

    系统变量autocommit是会话变量，即在一个命令行窗口将autocommit设置为0，另一个命令行窗口的autocommit的值还是1。

    当autocommit设置为0时，所有的SQL语句都是在一个事务中，直到显式地执行提交或者回滚时该事务才结束，并且同时开启另一个新事务。

  2. 当使用START TRANSACTION命令时可以隐式地关闭自动提交功能，该方法不会改变autocommit变量的值。

事务回滚

在上面的转账过程中，我们添加当转账用户的余额不足时撤销目标用户增加金额的操作，即进行事务回滚，步骤如下。

  1. 关闭MySQL的自动提交功能。
set autocommit = 0;

  1. 接着《事务机制的重要性》的例子，之前的。

    select * from tb_account;
    运行结果
    运行结果

  2. 接着进行一次转账并查看存款。

    call proc_transfer('A','B',700);
select * from tb_account;
    运行结果
    运行结果

可以看到在转账之后，A的账户扣款失败，但是B账户仍然多了700元，现在我们重新打开一个命令行窗口，并查看这两个账户的存款。

运行结果
运行结果

可见在新命令行窗口中并没有实际改变这两个变量的值，这是因为关闭了MySQL的自动提交后，若不手动提交，那么UPDATE操作将只会影响内存中的临时记录，并没有真正写入数据库文件。所以在当前命令行窗口执行SELECT语句时，获得的是临时记录，并不是实际数据表中的记录。此时的结果取决于接下来执行的操作，若执行ROLLBACK操作，那么就放弃当前的修改，若执行COMMIT，那么做出的修改会永久存到数据表文件中。

之后我们执行事务回滚操作，事务回滚操作的代码如下。

rollback;
select * from tb_account;
运行结果
运行结果

事务提交

当关闭自动提交之后，数据库开发人员可以根据需要提交更新操作，否则更新的结果不会提交到数据库文件中，成为数据库永久的组成部分。

显式提交

关闭自动提交功能之后，可以使用COMMIT命令显式提交更新语句，如上一节最后若把回滚语句换成提交语句COMMIT，就会变成以下结果。

运行结果
运行结果

此时该条记录就会永久地保存到数据库文件中，此时若再次打开一个新的命令行窗口并执行查看语句，会得到和上图相同的结果。

隐式提交

关闭自动提交后，若没有手动提交更新操作或者执行回滚操作，则当执行下表的命令时也将执行提交操作。

BEGINSET AUTOCOMMIT = 1LOCK TABLES
START TRANSACTIONCREATE DATABASE/TABLE/INDEX/PROCEDUREUNLOCK TABLES
TRUNCATE TABLEALTER DATABASE/TABLE/INDEX/PROCEDURE
RENAME TABLEDROP DATABASE/TABLE/INDEX/PROCEDURE

MySQL中的事务

在MySQL中，应用START TRANSACTION命令来标记一个事务的开始，语法如下。

start transaction;

通常START TRANSACTION后面跟着的是组成事务的SQL语句，并且要在所有语句都成功执行后执行提交事务COMMIT语句，下面通过一个具体实例演示事务的使用。

首先看一下A账户和B账户的账户余额，如下图，实现转账过程并调用的代码步骤如下。

运行结果
运行结果

  1. 创建存储过程，在存储过程中添加一个事务，实现从一个账户向另一个账户转账的过程，代码如下。

    delimiter //
create procedure prog_tran_account(in name_from varchar(50),in name_to varchar(50),in money int)
modifies sql data -- 修改数据库内数据
begin
    declare exit handler for sqlexception rollback;  -- 若发生数据库异常则回滚
    start transaction;
    update tb_account set balance = balance + money where name = name_to;
    update tb_account set balance = balance - money where name = name_from;
    commit;
end
//
    运行结果
    运行结果

  2. 尝试从A账户向B账户转账1200元，代码如下。可以看到转账失败了，但是A和B账户的资金总额没变，因为进行了事务回滚。

    call prog_tran_account('A','B',1200);
select * from tb_account;
    运行结果
    运行结果

  3. 这次我们尝试从A账户向B账户转账800元，代码如下。可以看到这次转账成功了。

    call prog_tran_account('A','B',800);
select * from tb_account;
    运行结果
    运行结果

回退点

默认情况下若回滚事务，所有执行成功的SQL操作都会撤销.若不想全部撤销，而只需要撤回一部分，就可以通过设置回退点，然后退回到回退点实现，创建回退点、退回该点的语法如下。

SAVEPOINT 回退点名;
ROLLBACK TO SAVEPOINT 回退点名;

如我们在转账之前先创建一个C账户，然后从A向B转账1200元（假设A的余额有1000元），代码如下。从结果可以看到，虽然转账失败了，但是C账户成功创建了。

delimiter //
create procedure prog_savepoint_account(in name_from varchar(50),in name_to varchar(50),in money int) modifies sql data
begin
    declare continue handler for sqlexception
        begin
            rollback to savepoint A;
            commit;
        end;
    start transaction;
    start transaction;
    insert into tb_account (name,balance) values ('C',0);
    savepoint A;
    update tb_account set balance = balance + money where name = name_to;
update tb_account set balance = balance - money where name = name_from;
    commit;
end //
delimiter ;
call prog_savepoint_account('A','B',1200);
select * from tb_account;
运行结果
运行结果

数据库管理系统采用锁的机制来管理事务。当多个事务同时修改同一数据时，只允许持有锁的事务修改该数据，其他事务只能“排队等待”，直到前一个事务释放其拥有的锁。下面对MySQL中提供的锁机制进行详细介绍。

锁机制

MySQL锁机制的基本知识

在同一时刻，可能会有多个客户端对中同一行操作，例如，有的客户端在读取该行数据，有的则尝试删除它。为了保证数据的一致性，数据库就要对这种并发操作进行控制，因此产生了锁的概念。

锁的类型

  1. 读锁

读锁也称为共享锁。它是共享的，或者说相互不阻塞的。多个客户端在同一时间可以同时读取同一资源，互不干扰。

  1. 写锁

写锁也成为排他锁。它是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。这是为了确保在给定的时间内，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源，保证安全。

在实际数据库中，随时都在发生锁定。例如，当某个用户修改某一部分数据时，MySQL就会通过锁定防止其他用户读取同一数据。在大多数时候，MySQL锁的内部管理是透明的。

读锁和写锁的区别如下表。

请求模式读锁写锁
读锁兼容不兼容
写锁不兼容不兼容

锁粒度

一种提高共享资源并发性的方式就是让指定对象更有选择性，也就是尽量只锁定部分数据，而不是所有的资源，这就是锁粒度的概念。它是指锁的作用范围，是为了对数据库中高并发的响应和系统性能两方面进行平衡提出的。

锁粒度越小，并发访问性能越高，越适合做并发更新操作（即采用InnoDB存储引擎的表适合做并发查询操作）；锁粒度越大，并发访问的效率越低，越适合做并发查询操作（即采用MyISAM存储引擎的表适合做并发查询操作）。

由于加锁也要消耗资源，所以需要注意，若系统花费大量的时间来管理锁，而不是存储数据，会得不偿失。

锁策略

锁策略是指在锁的开销和数据的安全性之间寻求平衡，但是这种平衡会影响性能，所以大多数数据库安全系统都没有提供更多的选择，一般都是在表上添加行级锁，并以各种复杂的方式来实现，以便在比较多的情况下提供更好的性能。

在MySQL中，每种存储引擎都可以实现自己的锁策略和颗粒度。因此，它提供了多种锁策略。在存储引擎的设计中，锁管理是非常重要的决定，它将颗粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时会失去对另外一个应用场景的良好支持。MySQL支持多个存储引擎，所以不用单一的通用解决方法。下面介绍两种不同的锁策略。

  1. 表级锁：表级锁是MySQL中最基本的锁策略，而且是开销最小的锁策略。它会固定整个表，一个用户在对表进行操作（如插入、删除、更新）前，需要先获得写锁，这会阻塞其他用户对表的所有读写操作。只有没有写锁时，其他读取的用户才能获取该锁，并且读锁之间是不会相互阻塞的。

另外，由于写锁比读锁的优先级高，所以一个写锁请求可能会被插入读锁队列的前面，但是读锁不能插入写锁的前面。

  1. 行级锁：行级锁可以最大限度地支持并发处理，同时也带来了最大的锁开销。在InnoDB或者一些其他存储引擎中实现了行级锁。行级锁只在存储引擎层中实现，而在服务器层中没有实现。服务器层中完全不了解存储引擎中的锁实现。

锁的生命周期

锁的生命周期就是在一个MySQL会话内，对数据进行加锁和解锁之间的时间间隔。锁的生命周期越长，并发性能就越低，反之并发性能就越高。另外，锁是数据库管理系统中的重要资源，需要占据一定的服务器内存，锁的周期越长，占用服务器内存的时间就越长；相反，占用的内存时间也就越短，因此应尽可能地缩短锁的生命周期。

MyISAM的表级锁

MySQL的MyISAM存储引擎不支持事务提交和回滚操作，所以当用户对数据库执行插入、删除、更新等操作时，这些变化的操作会立即保存在磁盘中。这在多用户环境会导致诸多问题。为了避免同一时间有多个用户对数据库中指定表进行操作，可以应用表锁定来避免用户在操作数据表时受到干扰。当且仅当用户释放表的操作锁定后，其他用户才可以访问修改后的数据表。

设置表级锁定事务的基本步骤如下：

  1. 为指定数据表添加锁定，语法如下。

    LOCK TABLE table_name lock_type,...;

    其中，lock_type表示锁定类型，该类型包括以读方式（READ）锁定表和以写方式（WRITE）锁定表。

  2. 用户执行数据表，可以添加、删除或者更改部分数据。

  3. 用户完成对锁定数据表的操作后，需要对该表进行解锁操作，释放该表的锁定状态，语法如下。

    UNLOCK TABLES;

下面将分别介绍如何以读方式和以写方式锁定数据表。

以只读方式锁定事务表

以读方式锁定数据表，是指设置锁定用户的其他操作方式，如删除、插入、更新等不被允许，直至用户进行解锁操作。下面我们使用只读方式锁定表tb_user，步骤如下。

  1. 创建数据表tb_user，存储引擎选择MyISAM，语法如下。

    create table tb_user(
    id int(10) not null auto_increment primary key,
    username varchar(30) not null,
    pwd varchar(30) not null
) ENGINE = 'MyISAM';
    运行结果
    运行结果

  2. 尝试向表中添加三组数据，语法如下。

    insert into tb_user (username, pwd) values 
('first','123456'),('second','222222'),('third','123123');
    运行结果
    运行结果

  3. 查看表中数据，语法如下。

    select * from tb_user;
    运行结果
    运行结果

  4. 以读方式锁定tb_user表，语法如下。

    lock tables tb_user read;
    运行结果
    运行结果

  5. 尝试查看数据和插入数据，代码如下。

    select * from tb_user;
insert into tb_user (username, pwd) values ('fourth','1234321');
    运行结果
    运行结果

可以看到加入读锁后表仍然可以读取，但是不能写入。

  1. 解锁tb_user表，代码如下。

    unlock tables;
    运行结果
    运行结果

  2. 再次尝试查看和插入数据表，代码如下。

    select * from tb_user;
insert into tb_user (username, pwd) values ('fourth','1234321');
    运行结果
    运行结果

可以看到在解锁后可以插入数据表。

  1. 最后查看数据表的结果，代码如下。

    select * from tb_user;
    运行结果
    运行结果

在LOCK TABLES的参数中，用户指定数据表以读方式（READ）锁定数据表的变体为READ LOCAL锁定，其与READ锁定的不同点是：该参数所指定的用户会话可以执行INSERT语句，它是为了方便MySQL dump工具而创建的变体形式。

InnoDB的行级锁

为InnoDB表设置锁比为MyISAM设置锁更为复杂，这是因为InnoDB既支持表级锁又支持行级锁。为InnoDB表设置行级锁也是使用LOCK TABLES命令，其使用方法同MyISAM表基本相同，这里不再赘述。

为InnoDB表设置行级锁有以下三种方式。

  1. 在查询语句中设置读锁，语法如下。

    SELECT 语句 LOCK IN SHARE MODE;

    如为采用InnoDB存储引擎的数据表tb_account在查询语句中设置读锁，可以使用如下语句：

    select * from tb_account lock in share mode;

  2. 在查询语句中设置写锁，语法如下。

    SELECT 语句 FOR UPDATE;

    如为采用InnoDB存储引擎的数据表tb_account在查询语句中设置写锁，语法如下：

    select * from tb_account for update;

  3. 在更新（包括INSERT、UPDATE和DALETE）语句中，InnoDB存储引擎会自动为更新语句添加隐式写锁。

通过以上三种方式为表设置行级锁的生命周期非常短暂，通常使用事务来延长行级锁的生命周期。

下面以写锁的方式演示如何使用行级锁。

  1. 在命令行窗口1输入下面的语句开启事务并开启写锁。

    start transaction;
select * from tb_account for update;
    运行结果
    运行结果

  2. 在命令行窗口2输入下面的语句开启事务并开启写锁。

    start transaction;
select * from tb_account for update;
    运行结果
    运行结果

  3. 提交命令行窗口1的事务，释放写锁。

    commit;
    运行结果
    运行结果

可以看到提交命令行窗口1的事务后，命令行窗口2的select语句执行了，中间间隔16.39秒。

死锁的概念及避免

死锁就是两个或多个处于不同序列的用户打算同时更新某相同的表时，因互相等待对方释放锁而导致几方一直处于等待状态。实际应用中，若两个处于不同序列的用户想要同时更新某个相同的表时最有可能产生死锁。更具体地讲，当两个事务同时等待对方释放所持有的资源，而导致两个事务都无法操作对方持有的资源，这样无限期的等待被称为死锁。

InnoDB表处理程序会自动检测死锁，若该处理程序发现用户在操作过程中产生死锁，会立即通过撤销方式来撤销其中一个事务，以便使死锁消失。

事务的隔离级别

锁机制有效地解决了事务的并发问题，但也影响了事务的并发性能（并发指数据库系统同时为多个用户提供服务的能力）。当一个事务将其操纵的数据资源锁定时，其他欲操作该资源的的事务必须等待锁定解除，才能继续进行，这就降低了数据库同时相应多个用户的速度，因此合理地选择隔离级别关系到一个软件的性能。下面将对MySQL事务的隔离级别进行详细介绍。

事务的隔离级别与并发问题

数据库系统提供了4种可选的事务隔离级别，它们与并发性能之间的关系如下图。

事务隔离级别和并发性能之间的关系
事务隔离级别和并发性能之间的关系

各种隔离级别的作用如下。

  1. Serializable（串行化）。采用此隔离级别，一个事务在执行过程中首先要将其欲操纵的数据锁定，待事务结束后再释放。若此时另一个事务也要操纵该数据，必须等待前一个事务释放锁定后才能继续进行，两个事务实际上是以串行化方式运行的。
  2. Repeatable Read（可重复读）。采用此隔离级别，一个事务在执行过程中能够看到其他事务已经提交的新插入记录，看不到其他事务对已有记录的修改。
  3. Read Committed（读已提交数据）。采用此隔离级别，一个事务在执行过程中能够看到其他事务未提交的新插入记录，也能看到其他事务已经提交的对已有事务的修改。
  4. Read Uncommitted（读未提交数据）。采用此隔离级别，一个事务在执行过程中能够看到其他事务未提交的新插入数据，也能看到其他事务提交的已插入数据。

综上所述，并非隔离级别越高越好，对于多数应用程序，只需要把隔离级别设置车工Read Committed即可（尽管会出现一些问题）。

设置事务的隔离级别

MySQL中使用SET TRANSACTION ISOLATION LEVEL命令设置事务的隔离级别。新的隔离级别将在下一个事务开始时生效。

设置事务隔离级别的语法格式如下。

SET {GLOBAL|SESSION} TRANSACTION ISOLATION LEVEL 具体级别;
上次编辑于:
贡献者: QI
2024 © ImQi1