概述我们的数据库一般都会并发执行多个事务多个事务可能会并发的对相同的一批数据进行增删改查操作可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题为了解决多事务并发问题数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制用一整套机制来解决多事务并发问题。接下来我们会深入讲解这些机制让大家彻底理解数据库内部的执行原理。1、Mysql事务及ACID特性详解1.1、事务及其ACID属性事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。原子性(Atomicity) 事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。一致性(Consistent) 在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。隔离性(Isolation) 数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。持久性(Durable) 事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。1.2、并发事务处理带来的问题1.2.1、更新丢失(Lost Update)或脏写当两个或多个事务选择同一行然后基于最初选定的值更新该行时由于每个事务都不知道其他事务的存在就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。1.2.2、脏读Dirty Reads一个事务正在对一条记录做修改在这个事务完成并提交前这条记录的数据就处于不一致的状态这时另一个事务也来读取同一条记录如果不加控制第二个事务读取了这些“脏”数据并据此作进一步的处理就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。一句话事务A读取到了事务B已经修改但尚未提交的数据还在这个数据基础上做了操作。此时如果B事务回滚A读取的数据无效不符合一致性要求。1.2.3、不可重读Non-Repeatable Reads一个事务在读取某些数据后的某个时间再次读取以前读过的数据却发现其读出的数据已经发生了改变、或某些记录已经被删除了这种现象就叫做“不可重复读”。一句话事务A内部的相同查询语句在不同时刻读出的结果不一致不符合隔离性1.2.4、幻读Phantom Reads一个事务按相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足其查询条件的新数据这种现象就称为“幻读”。一句话事务A读取到了事务B提交的新增数据不符合隔离性概括了就是2、Mysql事务隔离级别详解“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。隔离级别脏读(Dirty Read)不可重复读(NonRepeatable Read)幻读(Phantom Read)读未提交(Read uncommitted)可能可能可能读已提交(Read committed)不可能可能可能可重复读(Repeatable read)不可能不可能可能可串行化(Serializable)不可能不可能不可能数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读和“幻读”并不敏感,可能更关心数据并发访问的能力。查看当前数据库的事务隔离级别:showvariablesliketx_isolation;设置事务隔离级别settx_isolationREPEATABLE-READ;Mysql默认的事务隔离级别是可重复读用Spring开发程序时如果不设置隔离级别默认用Mysql设置的隔离级别如果Spring设置了就用已经设置的隔离级别3、Mysql锁机制详解锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除了传统的计算资源如CPU、RAM、I/O等的争用以外数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。4、Mysql锁优化建议4.1、锁分类4.1.1、从性能上分为乐观锁(用版本对比来实现)和悲观锁乐观锁设计表的时候在表中加一个专门的版本字段version在更新的时候带着这个version去更新返回的是0的表示的是没有更新成功需要再次处理类似于CAS。悲观锁比如事务1在执行update table set name‘a’ where id1在提交之前这行数据会被加上行锁属于悲观锁事务2更新这同一行数据就会一直被阻塞设计到了事务之间的等待就叫悲锁。4.1.2、从对数据库操作的类型分分为读锁和写锁(都属于悲观锁)还有意向锁读锁又叫共享锁S锁(Shared)针对同一份数据多个读操作可以同时进行而不会互相影响。比如select*fromTwhereid1lockinsharemode写锁又叫排它锁X锁(eXclusive)当前写操作没有完成前它会阻断其他写锁和读锁数据修改操作都会加写锁查询也可以通过for update加写锁比如select*fromTwhereid1forupdate意向锁Intention Lock又称I锁针对表锁主要是为了提高加表锁的效率是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁同时会给表设置一个标识代表已经有行锁了其他事务要想对表加表锁时就不必逐行判断有没有行锁可能跟表锁冲突了直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时逐行判断加表锁的方式效率很低。而这个标识就是意向锁。意向锁主要分为意向共享锁IS锁对整个表加共享锁之前需要先获取到意向共享锁。意向排他锁IX锁对整个表加排他锁之前需要先获取到意向排他锁。4.1.3、从对数据操作的粒度分分为表锁和行锁基本操作--建表SQLCREATETABLEmylock(idINT(11)NOTNULLAUTO_INCREMENT,NAMEVARCHAR(20)DEFAULTNULL,PRIMARYKEY(id))ENGINEMyISAMDEFAULTCHARSETutf8;--插入数据INSERTINTOtest.mylock(id,NAME)VALUES(1,a);INSERTINTOtest.mylock(id,NAME)VALUES(2,b);INSERTINTOtest.mylock(id,NAME)VALUES(3,c);INSERTINTOtest.mylock(id,NAME)VALUES(4,d);4.1.3.1、表锁–读/写锁每次操作锁住整张表。开销小加锁快不会出现死锁锁定粒度大发生锁冲突的概率最高并发度最低一般用在整表数据迁移、外部系统关闭不使用数据库的场景。加锁-查看是否加锁成功-删除表锁手动增加表锁locktable表名称read(write),表名称2read(write);查看表上加过的锁showopentableswhereTablexxx;删除表锁unlocktables;案例分析(加读锁当前session和其他session都可以读该表当前session中插入或者更新锁定的表都会报错其他session插入或更新则会等待如果 SHOW OPEN TABLES 返回了 mylock 这一行并且 In_use 1、Name_locked 0说明LOCK TABLE mylock READ 生效了当前会话已经给 mylock 表加上了一个表级读锁。In_use 1表示这张表当前有 1 个表锁/锁引用也就是当前会话持有这个读锁。Name_locked 0表示表名没有被锁没有 DROP TABLE、RENAME TABLE 这类 DDL 操作在占用表名。LOCK TABLE mylock READ; In_use 1, 是给 表里的数据 加了一个表级读锁。这张表现在可以被读但别人不能随便改这张表的数据比如 INSERT、UPDATE、DELETE 通常会被阻塞它锁的是“这张表的数据访问权限”。Name_locked 0 里的“表名锁”不是锁表里的数据而是锁 这张表的名字/对象本身。案例结论1、对MyISAM表的读操作(加读锁) ,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作4.1.3.2、行锁每次操作锁住一行数据。开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度最高。InnoDB与MYISAM的最大不同有两点InnoDB支持事务TRANSACTIONInnoDB支持行级锁createtableaccount(idBIGINTnotnullauto_incrementCOMMENT主键ID,nameVARCHAR(64)notnullCOMMENT账户名称,balanceDECIMAL(18,2)notnulldefault0.00COMMENT余额,primarykey(id))engineInnoDBdefaultCHARSETutf8mb4COMMENT账户表;INSERTINTOaccount(name,balance)VALUES(lilei,450.00),(hanmeimei,16000.00),(lucy,2400.00);行锁演示开启两个窗口一个session开启事务更新不提交另一个session更新同一条记录会阻塞更新不同记录不会阻塞案例结论InnoDB在执行查询语句SELECT时(非串行隔离级别)不会加锁。但是update、insert、delete操作会加行锁。简而言之就是读锁会阻塞写但是不会阻塞读。而写锁则会把读和写都阻塞。5、行锁与事务隔离级别案例分析隔离级别脏读(Dirty Read)不可重复读(NonRepeatable Read)幻读(Phantom Read)读未提交(Read uncommitted)可能可能可能读已提交(Read committed)不可能可能可能可重复读(Repeatable read)不可能不可能可能可串行化(Serializable)不可能不可能不可能5.1、读未提交settx_isolationread-uncommitted;5.2、读已提交settx_isolationread-committed;5.3、可重复读settx_isolationrepeatable-read;5.4、串行化settx_isolationserializable;6、间隙锁(Gap Lock)、临键锁(Next-key Locks)间隙锁锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read有办法解决幻读问题吗间隙锁在某些情况下可以解决幻读问题。假设account表里数据如下那么间隙就有 id 为 (3,10)(10,20)(20,正无穷) 这三个区间在Session_1下面执行 update account set name ‘zhuge’ where id 8 and id 18;则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据即id在(3,20]区间都无法修改数据注意最后那个20也是包含在内的。间隙锁是在可重复读隔离级别下才会生效。临键锁(Next-key Locks)Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。无索引行锁会升级为表锁(RR级别会升级为表锁RC级别不会升级为表锁)锁主要是加在索引上如果对非索引字段更新行锁可能会变表锁。session1 执行update account set balance 800 where name ‘lilei’;session2 对该表任一行操作都会阻塞住InnoDB的行锁是针对索引加的锁不是针对记录加的锁。并且该索引不能失效否则都会从行锁升级为表锁。锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)例如select * from test_innodb_lock where a 2 for update; 这样其他session只能读这行数据修改则会被阻塞直到锁定行的session提交结论Innodb存储引擎由于实现了行级锁定虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候Innodb的整体性能和MYISAM相比就会有比较明显的优势了。但是Innodb的行级锁定同样也有其脆弱的一面当我们使用不当的时候可能会让Innodb的整体性能表现不仅不能比MYISAM高甚至可能会更差。7、行锁分析通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况showstatuslikeinnodb_row_lock%;对各个状态量的说明如下Innodb_row_lock_current_waits: 当前正在等待锁定的数量Innodb_row_lock_time: 从系统启动到现在锁定总时间长度Innodb_row_lock_time_avg: 每次等待所花平均时间Innodb_row_lock_time_max从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits: 系统启动后到现在总共等待的次数对于这5个状态变量比较重要的主要是Innodb_row_lock_time_avg 等待平均时长Innodb_row_lock_waits 等待总次数Innodb_row_lock_time等待总时长尤其是当等待次数很高而且每次等待时长也不小的时候我们就需要分析系统中为什么会有如此多的等待然后根据分析结果着手制定优化计划。查看INFORMATION_SCHEMA系统库锁相关数据表-- 查看事务select*fromINFORMATION_SCHEMA.INNODB_TRX;-- 查看锁select*fromINFORMATION_SCHEMA.INNODB_LOCKS;-- 查看锁等待select*fromINFORMATION_SCHEMA.INNODB_LOCK_WAITS;-- 释放锁trx_mysql_thread_id可以从INNODB_TRX表里查看到killtrx_mysql_thread_id-- 查看锁等待详细信息showengineinnodbstatus\G;查询事务对应的线程id先通过select * from INFORMATION_SCHEMA.INNODB_TRX;这行语句查询到对应的事务的线程id复制出来方便有格式查看方便8、死锁settx_isolationrepeatable-read;演示死锁Session_1执行select * from account where id1 for update;Session_2执行select * from account where id2 for update;Session_1执行select * from account where id2 for update;Session_2执行select * from account where id1 for update;查看近期死锁日志信息show engine innodb status\G;大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务但是有些情况mysql没法自动检测死锁查看近期死锁日志信息showengineinnodbstatus\G;mysql show engine innodb status\G; *************************** 1. row *************************** Type: InnoDB Name: Status: 2026-10-13 16:27:52 0x4c0 INNODB MONITOR OUTPUT Per second averages calculated from the last 3 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 217 srv_active, 0 srv_shutdown, 171422 srv_idle srv_master_thread log flush and writes: 171639 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 237 OS WAIT ARRAY INFO: signal count 238 RW-shared spins 0, rounds 425, OS waits 169 RW-excl spins 0, rounds 426, OS waits 11 RW-sx spins 4, rounds 120, OS waits 4 Spin rounds per wait: 425.00 RW-shared, 426.00 RW-excl, 30.00 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2026-10-13 16:25:30 0x4c0 *** (1) TRANSACTION: TRANSACTION 111573, ACTIVE 48 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 43, OS thread handle 18100, query id 331602 localhost ::1 root statistics select * from account where id1 for update *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table test.account trx id 111573 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 8; hex 8000000000000001; asc ;; 1: len 6; hex 00000001b3a8; asc ;; 2: len 7; hex 74000001a30110; asc t ;; 3: len 5; hex 6c696c6569; asc lilei;; 4: len 9; hex 80000000000000c800; asc ;; *** (2) TRANSACTION: TRANSACTION 111572, ACTIVE 55 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 45, OS thread handle 1216, query id 331603 localhost ::1 root statistics select * from account where id2 for update *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table test.account trx id 111572 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 8; hex 8000000000000001; asc ;; 1: len 6; hex 00000001b3a8; asc ;; 2: len 7; hex 74000001a30110; asc t ;; 3: len 5; hex 6c696c6569; asc lilei;; 4: len 9; hex 80000000000000c800; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table test.account trx id 111572 lock_mode X locks rec but not gap waiting Record lock, heap no 14 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 8; hex 8000000000000002; asc ;; 1: len 6; hex 00000001b395; asc ;; 2: len 7; hex 6a0000019d0110; asc j ;; 3: len 6; hex 68616e6d6569; asc hanmei;; 4: len 9; hex 8000000000003e8000; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 111574 Purge done for trxs n:o 111570 undo n:o 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283210924882584, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283210924883456, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 111573, ACTIVE 190 sec 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 43, OS thread handle 18100, query id 331602 localhost ::1 root -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/os:, sync i/os: Pending flushes (fsync) log: 0; buffer pool: 0 15699 OS file reads, 113474 OS file writes, 111067 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 1 merges merged operations: insert 1, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 1 buffer(s) Hash table size 2267, node heap has 1 buffer(s) Hash table size 2267, node heap has 1 buffer(s) Hash table size 2267, node heap has 1 buffer(s) Hash table size 2267, node heap has 0 buffer(s) Hash table size 2267, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 22103818 Log flushed up to 22103818 Pages flushed up to 22103818 Last checkpoint at 22103809 0 pending log flushes, 0 pending chkp writes 110537 log i/os done, 0.00 log i/os/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 8585216 Dictionary memory allocated 170860 Buffer pool size 512 Free buffers 252 Database pages 256 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 15666, created 941, written 2643 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 256, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID8340, Main thread ID10004, state: sleeping Number of rows inserted 112685, updated 17, deleted 3, read 3704201 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT 1 row in set (0.00 sec) ERROR: No query specified mysql锁优化建议尽可能让所有数据检索都通过索引来完成避免无索引行锁升级为表锁合理设计索引尽量缩小锁的范围尽可能减少检索条件范围避免间隙锁尽量控制事务大小减少锁定资源量和时间长度涉及事务加锁的sql尽量放在事务最后执行尽可能低级别事务隔离