mysql优化之事务和锁

本文主要是介绍mysql优化之事务和锁，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

• 事务的基础
  • 什么是事务
    • 事务的必要性
    • 数据库系统引入事务的主要目的
  • 事务的应用场景
  • 支持事务的数据库引擎
  • 事务的特性
  • 事务的语法
    • 事务的简单操作
      • 事务的简单demo
      • 事务操作的简单小结
  • 事务的代码实现
• 事务的生命周期
  • 事务日志文件类型(innodb引擎)
  • 事务的执行流程
  • 查看事务日志以及查看事务日志状态
  • 重做日志
    • 重做日志的持久化特性
    • 实现重做日志的持久化特性
    • 重做日志的流程
  • 回滚日志
    • 回滚日志的原子性
    • 回滚日志实现原子性
    • 测试回滚操作
  • 重做日志和回滚日志的区别
• 锁
  • 概念
  • 类型
  • innoDB锁
  • innoDB锁类型
  • 锁对于语句的加锁
    • 排它锁锁加锁测试
      • 排它锁锁加锁测试小结
    • 共享锁锁加锁测试
      • 共享锁锁加锁测试小结
• 总结

事务的基础

什么是事务

1. 事务即 transaction，是数据库系统区别于文件系统的重要特性之一。
2. 在文件系统中，如果我们正在写文件，但是操作系统崩溃了，那么文件中的数据可能会丢失。但是数据库可以通过事务机制来确保这一点。

事务的必要性

假设有两个用户，不妨记为 a 和 b，假设 a 要给 b 转账 1000 元，那么只需要在表中把 a 对应的记录的相应字段减去 1000，给 b 对应的记录的相应字段增加 1000 即可。 
但是当我们给 a 对应的记录减去 1000，但是当执行给 b 对应的记录增加 1000 的时候，服务器突然出现了一些问题，导致该SQL 并未顺利执行就宕机了。
如果不启用事务，那么结果就是 a 对应的记录减少了 1000，但是 b 对应的记录并没有增加 1000，这是不符合常理的，于是，我们引入了事务机制来保证它的可靠性。

数据库系统引入事务的主要目的

事务会把数据库从一种一致性状态转换为另一种一致性状态。
在数据库提交的时候，可以确保要么所有提交都保存，要么所有修改都不保存。
事务可以用 事 来保证数据库的完整性：要么都做，要么不做来。

事务的应用场景

电商订单的生成,支付,以及其他需要事务的操作

支持事务的数据库引擎

innodb 支持事务
myisam 不支持=>伪事务

注意 : 在使用innodb引擎进行事务操作时,innodb都会执行锁住某一行数据,被锁住的数据不能被其他sql语句进行执行,只能先有当前事务执行完毕后,才能解锁,由其他sql语句执行,这种操作叫做行锁

事务的特性

详细的解释 :

1. 事务要求 ACID 的特性，即：原子性、一致性、隔离性、持久性。 
2. 所谓原子性，是指整个数据库的每个事务都是不可分割的单位。只有事务中的所有 SQL 语句都执行成功，才算整个事务成功，事务才会被提交。如果事务 中任何一个 SQL 语句执行失败，整个事务都应该被回滚。 
3. 所谓一致性，是指将数据库从一种一致性状态转换为下一种一致性状态。不允许数据库中的数据出现新老数据都有的情况，要么都是老数据，要么都是新数 据。用更书面化的表达就是：数据的完整性约束没有被破坏。
4. 所谓隔离性，是指一个事务的影响在该事务提交前对其他事务都不可见，它通过锁机制来实现。 
5. 所谓持久性，是指事务一旦被提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

通俗的解释 :

原子性 : 要么一起成功,要么一起失败
一致性 : 保证数据有一种状态转化为另一种状态
隔离性 : 互不打扰,互不干扰
持久性 : 保存在磁盘里面

事务的语法

1. 在 MySQL 命令行的默认设置下，事务是自动提交的，即执行了SQL 语句之后会马上执行 commit 操作，我们可以设置 set autocommit=0 来禁用当前回话 的自动提交。 
2. 还可以用 begin 、start transaction 来显式的开始一个事务。 
3. commit 在默认设置下是等价于 commit work 的，表示提交事务。 
4. rollback 在默认设置下等价于 rollback work，表示事务回滚。 
5. savepoint xxx 表示定义一个保存点，在一个事务中可以有多个保存点。 
6. release savepoint xxx 表示删除一个保存点，当没有该保存点的时候执行该语句，会抛出一个异常。 
7. rollback to [savepoint] xxx 表示回滚到某个保存点。

事务的简单操作

--查询事务自动提交状态 
show variables like '%commit%';

--全局修改 
set global autocommit=0; 
show global variables like 'autocommit'; 
--局部修改 
set session autocommit=0; 
show global variables like 'autocommit';

事务的简单demo

开启两个窗口，一个用于事务新增数据，一个用于事务提交前查询

第一次session1事务没有提交，session2直接查询数据

session1：

start transaction--开启事务 
insert into `user` (`name`,age,sex,`status`)values('harry',222,'男',1);

session2：

select * from `user` where name='harry';

测试结果:

第二次，session1提交事务，session2等到session1提交完成后查询数据

session1：

start transaction--开启事务 
insert into `user` (`name`,age,sex,`status`)values('will',222,'男',1); 
commit;--提交事务 
rollback;--回滚事务

session2:

select * from `user` where name='harry';

测试结果:

事务操作的简单小结

通过上面的例子，我们可以感受到事务的隔离性，也就是两个事务之间并不知道对方的存在，在 MySQL 的默认隔离级别下，当一个事务还没有提交的时候，其他事务是无法感知到数据的变化的。

事务的代码实现

这里使用php的代码实现

try{
  $conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
  mysql_select_db('test',$conn);
  mysql_query("set names 'GBK'"); //使用GBK中文编码;
  //开始一个事务
  mysql_query("BEGIN"); //或者mysql_query("START TRANSACTION");
  $sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
  $sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//这条我故意写错
  $res = mysql_query($sql);
  $res1 = mysql_query($sql2); 
  if($res && $res1){
    mysql_query("COMMIT");
    echo '提交成功。';
  }else{
    mysql_query("ROLLBACK");
    echo '数据回滚。';
  }
  mysql_query("END"); 
}catch(Exception e){
  mysql_query("ROLLBACK");
  echo '数据回滚。'
  mysql_query("END"); 
}

事务的生命周期

事务日志文件类型(innodb引擎)

重做日志

redo log ==> ib_logfile0 重新执行

回滚日志

undo log ==> ibdata ==> 回滚数据

事务的执行流程

日志先行,日志优先权大于实际数据操作,所有sql会先写日志,在写入数据的生命周期

具体的执行流程

1 创建日志文件
2 执行业务逻辑
2.1 根据实际的sql写入日志文件,但是并没有运行sql
3 刷新日志
4 写入数据到磁盘,也是sql执行的最终环节
5 写入CKP,验证数据,保证数据的一致性

查看事务日志以及查看事务日志状态

-- 查看事务日志 ： 
show engine innodb status\G; 
-- 查看日志文件设置状态 
show variables like 'innodb_%';

重做日志

重做日志的持久化特性

事务被提交，数据一定会被写入到数据库中并持久存储起来，通常来说当事务已经被提交之后，就无法再次回滚了。

实现重做日志的持久化特性

与原子性一样，事务的持久性也是通过日志来实现的，MySQL 使用重做日志（redo log）实现事务的持久性，重做日志由两部分组成，一是内存中的重做日志缓 冲区，因为重做日志缓冲区在内存中，所以它是易失的，另一个就是在磁盘上的重做日志文件，它是持久的。

重做日志的流程

当我们在一个事务中尝试对数据进行写时，它会先将数据从磁盘读入内存，并更新内存中缓存的数据，然后生成一条重做日志并写入重做日志缓存，当事务真正 提交时，MySQL 会将重做日志缓存中的内容刷新到重做日志文件，再将内存中的数据更新到磁盘上，图中的第 4、5 步就是在事务提交时执行的。

重做日志执行时间
在mysql中事务执行commit提交了之后，但是服务器挂了，数据还没有写入磁盘，在mysql重启服务之后会重新执行这个重做日志写入数据。

回滚日志

回滚日志的原子性

通俗的解释就是；一条绳子上的蚂蚱 专业点：事务就是一系列的操作，要么全部都执行，要都不执行

回滚日志实现原子性

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的 修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。

注意：

系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。 
在日志文件中:在事务中使用的每一条 INSERT 都对应了一条 DELETE，每一条 UPDATE 也都对应一条相反的 UPDATE 语句。

执行时间

1.手动执行回滚命令
2.事务执行后,提交前mysql出现异常,挂了,在下次重启服务时执行回滚操作

测试回滚操作

在事务提交前停止mysql服务

停止mysql服务

再一次开启mysql服务后查询我们新增的数据

未查询到新增的数据

重做日志和回滚日志的区别

到现在为止我们了解了 MySQL 中的两种日志，回滚日志（undo log）和重做日志（redo log）；
在数据库系统中，事务的原子性和持久性是由事务日志 （transaction log）保证的，在实现时也就是上面提到的两种日志，前者用于对事务的影响进行撤销，后者在错误处理时对已经提交的事务进行重做，它们能保 证两点：

1. 发生错误或者需要回滚的事务能够成功回滚（原子性）； 
2. 在事务提交后，数据没来得及写会磁盘就宕机时，在下次重新启动后能够成功恢复数据（持久性）； 在数据库中，这两种日志经常都是一起工作的，我们可以将它们整体看做一条事务日志，其中包含了事务的 ID、修改的行元素以及修改前后的值。

事务日志的格式:

锁

概念

1. 在开发多用户、数据库驱动的应用时，相当大的一个难点就是解决并发性的问题，目前比较常用的解决方案就是锁机制。 
2. 锁机制也是数据库系统区别于文件系统的一个关键特性。 
3. InnoDB 存储引擎和 MyISAM 存储引擎使用的是完全不同的策略，我们必须分开来讲。

类型

1. 相比其他数据库而言，MySQL 的锁机制比较简单，而且不同的存储引擎支持不同的锁机制。 
2. MyISAM 和 Memory 存储引擎使用的是表级锁,BDB 引擎使用的是页级锁，也支持表级锁。由于 BDB 引擎基本已经成为历史，因此就不再介绍了。 
3. InnoDB 存储引擎既支持行级锁，也支持表级锁，默认情况下使用行级锁。 
4. 所谓表级锁，它直接锁住的是一个表，开销小，加锁快，不会出现死锁的情况，锁定粒度大，发生锁冲突的概率更高，并发度最低。 
5. 所谓行级锁，它直接锁住的是一条记录，开销大，加锁慢，发生锁冲突的概率较低，并发度很高。
6. 所谓页级锁，它是锁住的一个页面，在 InnoDB 中一个页面为16KB，它的开销介于表级锁和行级锁中间，也可能会出现死锁，锁定粒度也介于表级锁和行级 锁中间，并发度也介于表级锁和行级锁中间。 
7. 仅仅从锁的角度来说，表级锁更加适合于以查询为主的应用，只有少量按照索引条件更新数据的应用，比如大多数的 web 应用。 
8. 行级锁更适合大量按照索引条件并发更新少量不同的数据，同时还有并发查询的应用，比如一些在线事务处理系统，即 OLTP。

innoDB锁

1. InnoDB 与 MyISAM 的相当大的两点不同在于： (1) 支持事务 (2) 采用行级锁
2. 行级锁本身与表级锁的实现差别就很大，而事务的引入也带来了很多新问题，尤其是事务的隔离性，与锁机制息息相关。
3. 对于事务的基本操作，对于不同隔离级别可能引发的问题，像脏读、不可重复读等问题我们上一节就已经举例说明了，这里就不再赘述了。
4. 数据库实现事务隔离的方式，基本可以分为两种：
   4.1 在操纵数据之前，先对其加锁，防止其他事务对数据进行修改。这就需要各个事务串行操作才可以实现。
   4.2 不加任何锁，通过生成一系列特定请求时间点的一致性数据快照，并通过这个快照来提供一致性读取。
5. 上面的第二种方式就是数据多版本并发控制，也就是多版本数据库，一般简称为 MVCC 或者 MCC，它是 Multi Version Concurrency Control 的简写。
6. 数据库的事务隔离越严格，并发的副作用就越小，当然付出的代价也就越大，因为事务隔离机制实质上是使得事务在一定程度上”串行化”,这与并行是矛盾 的。

innoDB锁类型

InnoDB 实现了下面两种类型的锁：

(1)共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 
(2)排他锁(X):允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁和排他写锁。

InnoDB 还有两种意向锁，即 Intention Lock，这两种锁都是表锁。意向锁是内部使用的，它是 InnoDB 内部加的，不用用户干预，意向锁分类如下：

(1)共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 
(2)排他锁(X):允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁和排他写锁。

这里有个锁兼容和冲突的概念，如果在加一个锁的时候，另一个锁可以加上去，那么就是锁兼容。如果加上一个锁之后，拒绝其他的锁加上，那么就是锁冲 突。

各种锁的兼容冲突情况如下：

(1)X 和所有锁都冲突 
(2)IX 兼容 IX 和 IS 
(3)S 兼容 S 和 IS 
(4)IS 兼容 IS、IX 和 S

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务，如果两者是冲突的，那么该事务就要等待锁释放。

对于 update、delete、insert 语句，InnoDB 会自动给设计到的数据集加排他锁即 X。

对于 select 语句，InnoDB 不会加任何锁。

我们可以使用如下语句来显式的给数据集加锁：

(1)共享锁(S):select * from t1 where ... lock in share mode; 
(2)排他锁(X):select * from t1 where ... for update;

我们可以用 select ...in share mode 来获得共享锁，主要用在数据依存关系时来确认某行记录是否存在，并确认没有人对这个记录进行 update 或者 delete 操 作。

我们可以使用 select... for update 来获得排他锁，它会拒绝其他事务在其上加其他锁。

锁对于语句的加锁

排它锁锁加锁测试

--给`user`表id为1的数据加排它锁 
start transaction--开启事务 
select * from `user` where id=1 for update;--给id为1的数据加排它锁 
commit;--提交事务 
rollback;--回滚事务

上面的结果我们并不能看出什么效果，但是如果我们同时开启两个session，一起去给同一数据加锁时排它锁的效果就非常明显了，如下测试：

排它锁锁加锁测试小结

上面的测试结果表名，当前事务给一行数据加锁，那么其他事务将不能在对数据做任何操作，即：不能读不能写，也不能与其他锁一起使用

共享锁锁加锁测试

session1:

--给`user`表id为1的数据加共享锁 
start transaction--开启事务 
select * from `user` where id=1 lock in share mode;--给id为1的数据加共享锁 
commit;--提交事务 
rollback;--回滚事务 

session2:

--给`user`表id为1的数据加共享锁 
start transaction--开启事务 
select * from `user` where id=1 lock in share mode;--给id为1的数据加共享锁 
commit;--提交事务 
rollback;--回滚事务 

--给`user`表id为1的数据加共享锁 
start transaction--开启事务 
update `user` set name='harry' where id=1 for update; 
commit;--提交事务 
rollback;--回滚事务

共享锁锁加锁测试小结

上面的测试结果表明，当前事务给一行数据加共享锁，那么其他事务可以加共享锁，但不能加排它锁。即：能读不能写，可以与共享锁一起使用，但不能与 能 排它锁一起使用

总结

从小节中,我知道里事务的由来,以及执行过程.明白了事务是先通过日志的方式,进行数据操作,等进行提交或者回滚了,才会执行写入到磁盘中,在这个过程中,我也了解事务日志的分类,主要有两种:重做日志和回滚日志.了解完事务后,我也知道锁的特性,分类以及在事务中的所起到的作用.

这篇关于mysql优化之事务和锁的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！