事务事务的四大特性数据库事务的几个特性原子性(Atomicity )、一致性( Consistency )、隔离性或独立性( Isolation)和持久性(Durabilily)简称就是 ACID原子性一系列的操作整体不可拆分要么同时成功要么同时失败一致性数据在事务的前后业务整体一致。转账。A:1000B:1000 转 200 事务成功; A800 B1200隔离性事务之间互相隔离。例如100个人下单就会有100个事务有一个失败了它的事务回滚不会影响其他事务。持久性一旦事务成功数据一定会落盘在数据库。事务的隔离级别越大的隔离级别并发能力越弱按 SQL 标准定义有幻读问题。按 MySQL InnoDB 的实际实现RR 级别没有幻读问题已被彻底解决。三个关键概念解释脏读读到另一个事务未提交的修改数据可能回滚。不可重复读同一事务内两次读取同一条记录结果不同因为其他事务修改并提交了。幻读同一事务内两次执行范围查询结果集数量不同因为其他事务插入或删除了记录。事务命令# 开启事务starttransaction# 提交事务commit# 回滚事务rollback# 手动提交starttransaction;updateaccountsetmoneymoney-1000wherenamejack;updateaccountsetmoneymoney1000wherenamerose;commit;#或者rollback;# 自动提交通过修改mysql全局变量“autocommit”进行控制showvariableslike%commit%;# 设置自动提交的参数为OFFsetautocommit0;-- 0:OFF 1:ON锁锁的分类从对数据操作的粒度分表锁/行锁从对数据操作的类型读\写分表锁偏读行锁偏写页锁介于以上两者之间了解即可乐观锁和悲观锁用处保证数据安全处理多用户并发访问。乐观锁每次去拿数据的时候都认为别人不会修改所以不会上锁但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。例如版本号或时间戳控制适用于多读少写的场景。一般通过加一个version字段去判断悲观锁每次去拿数据的时候都认为别人会修改所以每次在拿数据的时候都会上锁这样别人想拿这个数据就会block直到它拿到锁 DB的行锁、表锁等适用于数据一致性比较高的场景for update 行锁悲观锁会造成访问数据库时间较长并发性不好特别是长事务。乐观锁在现实中使用得较多厂商较多采用表锁偏向MyISAM存储引擎,开销小,加锁快;无死锁:锁定粒度大,发生锁中突的概率最高,并发度最低。简而言之就是读锁会阻塞写但是不会阻塞读。而写锁则会把读和写都阻塞。CREATEtablemylock(idINTNOTNULLPRIMARYKEYauto_increment,namevarchar(20))ENGINEmyisam;INSERTINTOmylock(name)VALUES(a);INSERTINTOmylock(name)VALUES(b);INSERTINTOmylock(name)VALUES(c);INSERTINTOmylock(name)VALUES(d);INSERTINTOmylock(name)VALUES(e);SELECT*FROMmylock;# 查看表是否被锁SHOWOPENtables;# 手动增加表锁# 给mylock上读锁book上写锁lockTABLEmylockread,bookwrite;# 解锁UNLOCKTABLES;# 读锁案例LOCKtablemylockread;# 两个session都可以查看mylock因为读锁是共享锁SELECT*FROMmylock;# 1100 - Table book was not locked with LOCK TABLES# 其他session可以读其他表但锁表的session只能读 被锁的那个表直至解锁SELECT*FROMbook;# 1099 - Table mylock was locked with a READ lock and cant be updated# 其他session更新该表的时候会阻塞等待直到该表的读锁被解锁UPDATEmylocksetnamea2WHEREid1;# 写锁案例LOCKTABLEmylockwrite;# 加锁的session可以 读/更新 已经加写锁的表# 也可以读其他表# 其他session读/写该表会阻塞等待SELECT*FROMmylock;UPDATEmylocksetnamea2WHEREid1;# 同样的解锁之后才能读其他表SELECT*FROMbook;行锁读锁 (S) 和 读锁 (S)可以共存多个事务同时加 S 锁互不阻塞读 (S) ↔ 写 (X)互相阻塞写锁 (X) 和 写锁 (X)互斥不能共存select...from...forupdateCREATETABLEtest_innodb_lock(aint(11),bVARCHAR(16))ENGINEinnodb;INSERTINTOtest_innodb_lockVALUES(1,b2);INSERTINTOtest_innodb_lockVALUES(3,3);INSERTINTOtest_innodb_lockVALUES(4,4000);INSERTINTOtest_innodb_lockVALUES(5,5000);INSERTINTOtest_innodb_lockVALUES(6,6000);INSERTINTOtest_innodb_lockVALUES(7,7000);INSERTINTOtest_innodb_lockVALUES(8,8000);INSERTINTOtest_innodb_lockVALUES(9,9000);INSERTINTOtest_innodb_lockVALUES(1,b1);SELECT*FROMtest_innodb_lock;CREATEINDEXtest_innodb_a_indONtest_innodb_lock(a);CREATEINDEXtest_innodb_lock_b_indONtest_innodb_lock(b);SETautocommit0;UPDATEtest_innodb_lockSETb4001WHEREa4;# 在该session commit之后其他session也得commit才能读到这条更新的数据# 证明了mysql的隔离级别是 可重复读存在虚读的问题COMMIT;UPDATEtest_innodb_lockSETb4002WHEREa4;# 该session更新未提交其他session更新的话就进入阻塞# 等该session提交之后解除阻塞其他session才能执行更新COMMIT;UPDATEtest_innodb_lockSETb4005WHEREa4;# 该session更新的是 4 这行# 其他session更新其他行可以成功执行不进入阻塞COMMIT;索引失效行锁变表锁# b是有索引的而且是varchar类型# varchar类型在where后如果不加 依然能执行成功# 这是由于mysql内部做了类型转换但该操作就引起了索引失效# 进一步使行锁变成表锁其他session在更新其他行数据就会造成阻塞updatetest_innodb_lockseta41whereb4000;# 或者这个字段本身就是没有索引也会变成表锁间隙锁间隙锁Gap Lock是 MySQL InnoDB 在 REPEATABLEREAD可重复读隔离级别下用来解决幻读问题的核心武器。间隙锁锁住的不是记录本身而是记录与记录之间的“空隙”Gap目的是防止其他事务在这个空隙中插入新数据。# 如果事务A执行了SELECT*FROMuserWHEREidBETWEEN5AND10FORUPDATE;# InnoDB 不仅会锁住 id5 和 id10 这两行还会锁住 (5, 10) 这个间隙阻止其他事务插入 id6,7,8,9# 测试间隙锁# 事务1STARTTRANSACTION;select*fromuserwhereidBETWEEN2and5forupdate;# 事务2# 此时会一直运行中不会执行成功STARTTRANSACTION;insertintouservalues(4,lj);COMMIT;# 事务1COMMIT;# 事务2就会自动执行成功了间隙锁的触发条件什么时候会加间隙锁普通 SELECT快照读不加通过 MVCC 实现一致性读不需要锁其他SELECT … FOR UPDATE当前读、UPDATE / DELETE当前读 范围查询时会加间隙锁INSERT会加间隙锁插入前会检查是否有冲突的间隙锁关键点间隙锁只存在于REPEATABLE READ级别且只在范围查询非唯一索引或范围条件时触发。如果 WHERE 条件是唯一索引等值查询且记录存在则只加行锁不加间隙锁。间隙锁危害并发性能下降插入操作被阻塞死锁风险增加多个事务互相等待间隙锁事务套锁还是锁套事务如果你喜欢别人的东西就把它拿过来辩护律师总是找得到的。腓特烈二世
