mysql 事务操作与锁机制

mysql 事务操作与锁机制

🐟mysql 事务引入

mysql 事务是由存储引擎支持实现的，目前所主流的孙处引擎只有InnoDB支持mysql 的事务操作。

到底什么是事务呢？

这是一种mysql 的一种语法操作。通过msql 的一种代码操作后，然后对成批的需要执行的sql语句进行成批的处理。所达到的效果就是这些sql语句要么全部执行，要么全部不执行。

想一想，如果你的一推mysql 语句结合起来是要完成某一个具体的操作，但是如果你的一条语句出错导致无法执行，另一条语句没有语法错误，执行完毕，这样就可能在某些数据上造成差错。

比如一个类似银行转账的操作，一条进行转账的语句成功执行了，李四的钱成功被加上了转账的数目，但是在你这里进行扣款的操作突然出现异常，导致另一条sql语句没有进行执行，这样就会导致数据异常。

于是，我们可以用事务来控制程序的执行，要么一起执行，要么都别执行，在某些方面可以保证数据的安全。当然这只是一个举例，银行的数据维护交易要远远保险。

事务可以用来管理DDL,DML,DCL操作。也就是对数据库的查询，增删改，数据库的控制（包括数据库的授权，回滚，以及事务提交）都可以进行一个管理。

而在我们的mysql 中的一般系统语法，我们执行完一个语句后，事务是自动提交的，但是我们可以对参数进行修改，改变为手动提交。具体的就看下面的举例。

mysql 事务具体的操作

在mysql的事务操作主要有三种

查看自己数据库的事务提交模式

select @@autocommit;

这个系统变量的值是1，代表你的事务操作是自动提交的，于是我们可以设定为手动提交。将它的值改为0就可以了。

set @@autocommit = 0;

我们可以验证一下是不是全部执行成功，事务才可以提交成功。

我们先创建一个表

create database if not exists mydb12_transcation;
use mydb12_transcation;
create table account(
    id int primary key,
    name varchar(20),
    money double

);
insert into account values(1,"张三",1000);
insert into account values(2,"李四",2000);

我们写两条语句，一条没有错误，一条发生错误。看看可不可以成功执行。

begin;
update account set money = money-200 where id =1;
update  set money = money +200 where id =3;-- 执行完之后提交事务
commit ;

我们需要去把这样的dml的语法故意写错，如果你觉得语法错误太难看去给一个不存在数据赋值，这样是可以正确执行的，因为dml操作的一个特点就是只要语法正确就可以执行，不会报错。如果你要修改的数据不存在，那么就修改不成功就完了，它不会给你提示不存在的报错信息。这是需要注意的一点，因为如果忽略这点可能对你的验证操作造成迷惑。

我们执行上面事务包围的语句，其实这个时候你的autocommit是1也是没有关系的，你提交这次数据操作的时候，就可以不用再commit进行，如果你设定为1的话，就需要进行commit，这样就完整的进行了一个事务提交。

我执行上面的语句是肯定会报一个错的，但是我们需要观察的是上面那条正确的语句有没有成功的执行，或者对表数据造成改变。

没有改变。这样证明了一条语句没有成功执行的时候，整个包围的sql语句也都不会成功执行。

现在我们不使用事务去执行。你可以设置一个其它的引擎

alter table account engine = myisam;
update account set money = money-200 where name = “张三”;
update set money = money +200 where name = “李四”;

myisam 是不支持事务操作的，你可以这样验证，当然你可以把autocommit设置为0,不进行提交，也能达到相同的效果。但是其实意义还是不一样的。

现在我执行，也一样会报错。我们观察是不是对表有改变。

你看这样即使第二条语句有问题，第一条语句也可以正常执行，对表数据造成改变。给对方转账，结果对方的钱没有增加，反而自己的钱还少了。这样的话，就会出现数据错误的问题

这样举例验证就说明一个非事务支持和事务支持操作的区别。

mysql 事务具有的某些特性

但是呢，事务支持真的就十全十美吗?并不是。

我们从特性上面研究一下它的隔离级别

mysql 的隔离级别

隔离级别主要体现在数据的读写操作的权限上，一共分为四个隔离级别如下图。

读未提交的，就是一个事务可以读取到另一个未提交事务的数据，这是级别中最低的级别。这种会造成一个对数据的脏读。

读已提交，就是一个事务要等到另一个事务提交后才可以读到数据。这样虽然可以避免脏读，按时会造成不可重复读。

可重复读，就是开始读取数据时，不可以再进行修改，可以避免脏读，不可重读读的发生，但是会造成幻读。

串行，这是最高的隔离级别，事务进行串行化。可以避免脏读，不可重复读，以及幻读，但是这种的效率比较低的。

mysql 默认的隔离级别时可重复读。

这四种隔离级别对应的sql语句如下，可以这样设置。

--  查看数据库的隔离级别
show variables like "%isolation%";set session transaction isolation  level  read uncommitted;set session transaction isolation  level  read committed;set session transaction isolation  level  repeatable read;set session transaction isolation  level serializable;

读未提交的脏读

可以选择简单操作区验证这些隔离级别的特点。打开两个终端。

下面我们演示脏读，为了逼格和方便，就直接再终端简单敲一波。

我们在一张表中查看李四的账户钱款

账户余额两千，老板说要给李四发200块钱。于是老板开始操作了。

在这之前呢，我们需要对两个终端都设置下事务的提交模式，以及数据库引擎（默认时innodb就不必设置了），还要设置隔离模式为read uncommitted。并且还要进入begin，这些都需要在两个终端同步。

为什么需要这样做呢？同一个数据库不是设置一个就行了吗？原因就是你打开一个终端，该终端只保存了当前的环境，并不会对你在另一个终端的改变做出更新。（我是这么理解的，不知道有没有错）这样我们才能模拟真实的场景。

两个终端都需要这样操作。

然后两个终端都需要进行到这个效果图。然后我们在其中一个终端开始操作。老板给李四打钱。

然后李四查看自己账户的钱

一共两千2200,李四寻思可以区买一辆小电动车。于是就去买了。
画面转到老板这里，老板想了想，上次酒钱我好像还了，先撤回来吧，幸好还没有提交事务。于是撤回来了。

李四呢，到了商家那里要买电动车，正好要买2200的电动车，开始支付，但是余额不足。于是李四查看自己的账户。这样操作完之后我们对李四查看的窗口终端进行commit,另外一个终端已经回滚结束。我们也要结束一下李四这边的。

怎么只剩下两千了？于是李四懵了。

读已提交引起的不可重复读

我们需要在两个终端分别提升一下隔离级别。然后再开启一个事务。

没有买到电动车的李四准备要回家，在路上朋友打电话要聚会。于是李四想了想，干脆就请用这点钱请朋友吃饭吧！朋友都说老板大气。

李四准备去干饭了。妻子去逛商城了，看见一件非常漂亮的衣服。只要1999，只要1999！妻子买了。用李四的账户。

李四觉得很有面子，他此时谨慎的查看了下自己的账户，确认一下。

没有错，我放心了。
妻子这边数据库事务之后才提交。

于是李四高高兴兴地去请朋友吃饭了。大酒大肉，喝的半醉。

于是服务员问可以结账了吗，李四说结账！结果却显示余额不足。李四觉得见鬼了，于是又查了下账户。什么只有一块钱？

可重复读引起的幻读

两个窗口分别再次提升隔离级别，并开启事务。

这种情况出现的问题就是在事务提交之前和提交之后出现的数据不一样。

现在张三的老婆也做了同样的事情。只不过要消费999。因为张三老婆听说李四老婆上次的漂亮衣服调价了，现在只要999。于是也去买了。张三听说李四的时期后，决定安慰一下李四，要请他一个人吃1000块钱的大餐。

张三查看自己的账户，正好一千。此时老婆已经消费。

老婆事务提交了，但是张三这边还是一千。

吃完饭后，张三准备付款。看了看自己的账户。

好，海域一千，然后他提交事务。结果显示余额不足。此时，他又查看了下账户。

串行化安全

同样在两个终端分别提升隔离级别和开启一个事务。

为什么说安全呢？因为开启这种事务的时候，一个操作没有进行事务提交的时候，另一个操作时=是不能进行的。

进入情景。张三懵了。但是张三决定给李四买一根棒棒糖。
此时呢，张三老婆刚买完衣服，开开心心，看到路边小摊卖棒棒糖额，于是去买。

张三确认了一下还有一块钱。

于是张三要买。

但是发现卡住了，没响应。

此时张三老婆这边提交了事务

随后张三这边得到消息，余额不足。

这些都是隔离级别的特点。只要理解可能出现的问题就可以。最后一种比较安全，但是不可否认，这种串行的隔离级别是效率一定比较低的。mysql 默认的是可重复读的隔离级别，对一些需要解决的问题，可以用锁进行解决规避。

🔒锁引入

锁，分为表锁和行锁，读锁，写锁。。这显然是故名思意的。

这种锁的机制比较明显的体现在数据库引擎的支持上。

所以我们主要关注的还是MyISAM和InnoDB两大搜索引擎。

行级别的锁肯定和表级别的锁有不同的特点。

表级别一定是加锁比较快的，直接加在表上，所以开销比较少。整个表都加了锁，自然会发生冲突的概率高，并发自然非常低。

行级别的锁自然开销大，比较慢，但是并发搞，冲突少。

表级锁案例

那就先建立两张表

drop database if exists  mydb14_lock;
create database mydb14_lock ;
 
use mydb14_lock;
  
create table `tb_book`(`id`int(11) auto_increment,`name`varchar(50)defaultnull,`publish_time` date defaultnull,`status`char(1)defaultnull,
  primary key(`id`)) engine=myisam default charset=utf8 ;
 
insert into tb_book(id, name, publish_time, status)values(null,'java编程思想','2088-08-01','1');
insert into tb_book(id, name, publish_time, status)values(null,'solr编程思想','2088-08-08','0');

create table `tb_user`(`id`int(11) auto_increment,`name`varchar(50)defaultnull,
  primary key(`id`)) engine=myisam default charset=utf8 ;
 
insert into tb_user(id, name)values(null,'令狐冲');
insert into tb_user(id, name)values(null,'田伯光');

读锁

很简单。

我们这次还是在终端操作，我们这样操作一下。

可以看到，加读锁之后，我们可以读取表，但是不可以修改。

然后我们再开启一个终端，进行同样的操作，发现我们也可以加锁，读，但是不能修改。注意这一点。

读锁都可以加，说明了他是一种共享锁。

还需要注意的事情是，我们再tb_user上加了锁，那么我们能不能再给另一张表进行操作呢？

我们发现就连简单的查询也是不可以，所以以此亦可以说明，我们这样的读锁说明了一个资源的占用，并且你只能操作当前锁的资源。

写锁

ok,我们先把锁解开

然后我们加一个写锁。

然后我们进行读写的尝试

我们发现既可以读也可以写。

然后我们在另一个终端尝试操作这张被锁住的表

但是我们发现卡住了，所以这其实不是一个共享的锁。同样的尝试，也是不能再加写锁的，所以这其实是一个互斥锁。除非你释放另一个终端的锁。

行级锁案例

InnoDB及既支持行锁，也支持表锁。表锁的特点基本和MyISM是一样的，所以不在赘述。我们主要看这里行锁。

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。

InnoDB 实现了以下两种类型的行锁。
共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；

然后我们还是创建表

drop table if exists test_innodb_lock;
create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1))engine = innodb ;
 
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
 
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

注意要分开执行。

我们在两个终端执行，按照事务级别
达到同样的操作。

此时开启事务后先进行一个查询。发现两张表都可以查到数据。

但是当我们的一张表修改，事务还没提交，因为我们手动提交。然后另一张表进行修改操作。

我们发现这样是无法做到修改的（对当前行）。这是由于当我们进行增删改的时候，会自动加一个排他锁。

当前是其它行是可以操作的。因为我们是行锁。

读锁和写锁也是一样同上的道理，不再赘述。