我更新了博客站点个人站点点击访问后续记录在这里输出。mysql 事务操作与锁机制mysql 事务引入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 databaseifnot exists mydb12_transcation;use mydb12_transcation;create tableaccount(id int primary key,namevarchar(20),money double);insert into accountvalues(1,张三,1000);insert into accountvalues(2,李四,2000);我们写两条语句一条没有错误一条发生错误。看看可不可以成功执行。begin;update accountsetmoneymoney-200where id1;updatesetmoneymoney200where id3;--执行完之后提交事务 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 事务具有的某些特性2023/3/3补充今天看到这个mysql的一致性有点迷糊。定义是这样解释了但是感觉其实还是不够形象。今天找到一段非常牛逼的解释。能看懂的话牛可以了解到非常牛逼这段解释。所谓一致性是数据库处理前后结果应与其所抽象的客观世界中真实状况保持一致。这种一致性是一种需要管理员去定义的规则。管理员如何指定规则数据库就严格按照这种规则去处理数据。就拿那个老掉牙的AB转账来进一步解释如果说AB账户总金额5000就是数据库的一致性规则那么我能不能把A账户转走10000给B让B账户有10000而A剩下-5000从数学上来看完全正确但这显然是不符合常理的。而这种常理就是所谓的一致性。但是呢事务支持真的就十全十美吗?并不是。我们从特性上面研究一下它的隔离级别mysql 的隔离级别隔离级别主要体现在数据的读写操作的权限上一共分为四个隔离级别如下图。读未提交的就是一个事务可以读取到另一个未提交事务的数据这是级别中最低的级别。这种会造成一个对数据的脏读。读已提交就是一个事务要等到另一个事务提交后才可以读到数据。这样虽然可以避免脏读但是会造成不可重复读。可重复读就是开始读取数据时不可以再进行修改可以避免脏读不可重读读的发生但是会造成幻读。mysql默认串行这是最高的隔离级别事务进行串行化。可以避免脏读不可重复读以及幻读但是这种的效率比较低的。mysql 默认的隔离级别时可重复读。这四种隔离级别对应的sql语句如下可以这样设置。--查看数据库的隔离级别 show variables like%isolation%;setsession transaction isolation level read uncommitted;setsession transaction isolation level read committed;setsession transaction isolation level repeatable read;setsession transaction isolation level serializable;读未提交的脏读可以选择简单操作区验证这些隔离级别的特点。打开两个终端。下面我们演示脏读为了逼格和方便就直接再终端简单敲一波。我们在一张表中查看李四的账户钱款账户余额两千老板说要给李四发200块钱。于是老板开始操作了。在这之前呢我们需要对两个终端都设置下事务的提交模式以及数据库引擎默认时innodb就不必设置了还要设置隔离模式为read uncommitted。并且还要进入begin这些都需要在两个终端同步。为什么需要这样做呢同一个数据库不是设置一个就行了吗原因就是你打开一个终端该终端只保存了当前的环境并不会对你在另一个终端的改变做出更新。我是这么理解的不知道有没有错这样我们才能模拟真实的场景。两个终端都需要这样操作。然后两个终端都需要进行到这个效果图。然后我们在其中一个终端开始操作。老板给李四打钱。然后李四查看自己账户的钱一共两千2200,李四寻思可以区买一辆小电动车。于是就去买了。画面转到老板这里老板想了想上次酒钱我好像还了先撤回来吧幸好还没有提交事务。于是撤回来了。李四呢到了商家那里要买电动车正好要买2200的电动车开始支付但是余额不足。于是李四查看自己的账户。这样操作完之后我们对李四查看的窗口终端进行commit,另外一个终端已经回滚结束。我们也要结束一下李四这边的。怎么只剩下两千了于是李四懵了。读已提交引起的不可重复读我们需要在两个终端分别提升一下隔离级别。然后再开启一个事务。没有买到电动车的李四准备要回家在路上朋友打电话要聚会。于是李四想了想干脆就请用这点钱请朋友吃饭吧朋友都说老板大气。李四准备去干饭了。妻子去逛商城了看见一件非常漂亮的衣服。只要1999只要1999妻子买了。用李四的账户。李四觉得很有面子他此时谨慎的查看了下自己的账户确认一下。没有错我放心了。妻子这边数据库事务之后才提交。于是李四高高兴兴地去请朋友吃饭了。大酒大肉喝的半醉。于是服务员问可以结账了吗李四说结账结果却显示余额不足。李四觉得见鬼了于是又查了下账户。什么只有一块钱可重复读引起的幻读两个窗口分别再次提升隔离级别并开启事务。这种情况出现的问题就是在事务提交之前和提交之后出现的数据不一样。现在张三的老婆也做了同样的事情。只不过要消费999。因为张三老婆听说李四老婆上次的漂亮衣服调价了现在只要999。于是也去买了。张三听说李四的时期后决定安慰一下李四要请他一个人吃1000块钱的大餐。张三查看自己的账户正好一千。此时老婆已经消费。老婆事务提交了但是张三这边还是一千。吃完饭后张三准备付款。看了看自己的账户。好海域一千然后他提交事务。结果显示余额不足。此时他又查看了下账户。串行化安全同样在两个终端分别提升隔离级别和开启一个事务。为什么说安全呢因为开启这种事务的时候一个操作没有进行事务提交的时候另一个操作时是不能进行的。进入情景。张三懵了。但是张三决定给李四买一根棒棒糖。此时呢张三老婆刚买完衣服开开心心看到路边小摊卖棒棒糖额于是去买。张三确认了一下还有一块钱。于是张三要买。但是发现卡住了没响应。此时张三老婆这边提交了事务随后张三这边得到消息余额不足。这些都是隔离级别的特点。只要理解可能出现的问题就可以。最后一种比较安全但是不可否认这种串行的隔离级别是效率一定比较低的。mysql 默认的是可重复读的隔离级别对一些需要解决的问题可以用锁进行解决规避。2023/3/3补充脏读和幻读的区别假设有两个事务T1和T2读脏数据T1 修改一个数据T2 随后读取这个数据。如果 T1 撤销了这次修改那么 T2 读取的数据是脏数据。幻影读T1 读取某个范围的数据T2 在这个范围内插入新的数据T1 再次读取这个范围的数据此时读取的结果和和第一次读取的结果不同。 是指当某个事务在读取某个范围内的记录是另外一个事务又在该范围内插入了新记录当之前的事务再次读取该范围的记录时会产生幻行。例如第一个事务对一个表中的数据进行了修改这种修改涉及到表中的全部数据行。同时第二个事务也修改这个表中的数据这种修改是向表中插入一行新数据。那么以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行就好象发生了幻觉一样。幻读的重点在于新增或者删除同样的条件, 第 1 次和第 2 次读出来的记录数不一样。而读脏数据是读到的数据是已经被撤销修改的数据所以是脏数据。我个人认为幻读比较严格的解释应该是这样的事务 A 根据条件查询得到了 N 条数据但此时事务 B 删除或者增加了 M 条符合事务 A 查询条件的数据这样当事务 A 再次进行查询的时候真实的数据集已经发生了变化但是A却查询不出来这种变化因此产生了幻读。2023/12/20新增理解不可重复读一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据事务 B 在事务 A 多次读取的过程中对数据作了更新并提交导致事务 A 多次读取同一数据时结果 不一致。幻读一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级但是系统管理员 B 就在这个时候插入了一条具体分数的记录当系统管理员 A 改结束后发现还有一条记录没有改过来就好像发生了幻觉一样这就叫幻读。不可重复读的和幻读很容易混淆不可重复读侧重于修改幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行解决幻读需要锁表。锁引入锁分为表锁和行锁读锁写锁。。这显然是故名思意的。这种锁的机制比较明显的体现在数据库引擎的支持上。所以我们主要关注的还是MyISAM和InnoDB两大搜索引擎。行级别的锁肯定和表级别的锁有不同的特点。表级别一定是加锁比较快的直接加在表上所以开销比较少。整个表都加了锁自然会发生冲突的概率高并发自然非常低。行级别的锁自然开销大比较慢但是并发搞冲突少。表级锁案例那就先建立两张表drop databaseifexists mydb14_lock;create database mydb14_lock;use mydb14_lock;create tabletb_book(idint(11)auto_increment,namevarchar(50)defaultnull,publish_timedatedefaultnull,statuschar(1)defaultnull,primarykey(id))enginemyisamdefaultcharsetutf8;insert intotb_book(id,name,publish_time,status)values(null,java编程思想,2088-08-01,1);insert intotb_book(id,name,publish_time,status)values(null,solr编程思想,2088-08-08,0);create tabletb_user(idint(11)auto_increment,namevarchar(50)defaultnull,primarykey(id))enginemyisamdefaultcharsetutf8;insert intotb_user(id,name)values(null,令狐冲);insert intotb_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 tableifexists test_innodb_lock;create tabletest_innodb_lock(idint(11),namevarchar(16),sexvarchar(1))engineinnodb;insert into test_innodb_lockvalues(1,100,1);insert into test_innodb_lockvalues(3,3,1);insert into test_innodb_lockvalues(4,400,0);insert into test_innodb_lockvalues(5,500,1);insert into test_innodb_lockvalues(6,600,0);insert into test_innodb_lockvalues(7,700,0);insert into test_innodb_lockvalues(8,800,1);insert into test_innodb_lockvalues(9,900,1);insert into test_innodb_lockvalues(1,200,0);create index idx_test_innodb_lock_id ontest_innodb_lock(id);create index idx_test_innodb_lock_name ontest_innodb_lock(name);注意要分开执行。我们在两个终端执行按照事务级别达到同样的操作。此时开启事务后先进行一个查询。发现两张表都可以查到数据。但是当我们的一张表修改事务还没提交因为我们手动提交。然后另一张表进行修改操作。我们发现这样是无法做到修改的对当前行。这是由于当我们进行增删改的时候会自动加一个排他锁。当前是其它行是可以操作的。因为我们是行锁。读锁和写锁也是一样同上的道理不再赘述。2024/9/6 补充参见不和重复读和幻读的区别线上网站点击访问
