mysql-事务-锁

什么是事务一组操作要么全部执行要么全部不执行保证数据最终一致性。ACID特性原子性Atomicity事务操作要么全部执行成功要么全部执行失败。由undo Log 日志保证。执行一半怎么办一致性Consistency事务最终目标由AID特性与正确业务逻辑代码保证。 业务规则被破坏隔离性Isolation事务并发执行时保证事务之间数据隔离。由锁与mvcc机制保证。 并发冲突怎么办持久性Durability事务提交后数据永久保存。由redo Log 双写缓冲区保证。 崩溃后数据还在吗隔离级别与并发问题并发问题一个事务修改了另一个未提交事务已经修改过的数据脏写当前事务更新的数据覆盖了其他事务的更新的数据-场景事务A读取到数据a1由程序进行a1逻辑计算后直接更新此行数据。假如事务A由程序进行逻辑计算时表中数据a1被其他事务更新此时事务A依旧使用旧数据进行逻辑计算并更新此行数据。-解决1逻辑操作使用SQL在同一事务中一起处理。2乐观锁更新数据时使用版本号判断数据是否被修改过。脏读事务A读到事务B已经修改但未提交的数据如果事务B回滚事务A读到数据无效不符合数据一致性。MVCC解决幻读事务A读取到事务B提交的新增数据。-场景事务B新增的数据满足事务A范围查询条件导致事务A两次查询范围结果不一致。事务B删除满足事务A范围查询条件数据。-解决间隙锁不可重复读事务A同一条件查询语句在不同时刻读取的同一行数据结果不同。MVCC解决隔离级别读未提交read uncommit 可能会出现脏读不可重复读幻读问题读已提交read commit 解决脏读问题可能会出现不可重复读幻读问题。读取数据时使用语句级快照核心机制是 “读取已提交的数据版本”实现方式 MVCC多版本并发控制 Read View读视图可重复读repeatable read 解决脏读、不可重复读问题。幻读问题引入临建锁解决。读取数据时使用事务级快照串行化serializable 所有事务串行化执行。执行更新或查询数据时其他事务都无法更新。同一条数据读写都会加锁x/s锁。 范围查询时范围内的所有行包括每行记录所在的间隙区间范围都会被加锁解决幻读。语句级快照仅当前SELECT语句。事务中每条SELECT语句执行时查询到的数据结果,rc事务级快照整个事务使用同一个快照。事务中第一条SELECT语句查询到的数据结果,rr快照读:读取历史数据当前读读取当前版本数据事务中查询数据使用快照读修改数据时(update、delete)或加锁查询使用当前读当前版本锁 保证数据并发访问一致性与有效性从性能上区分**乐观锁(用版本对比来实现)悲观锁读锁写锁从对数据库操作的类型区分**读锁共享锁S锁(Shared)针对同一份数据多个读操作可以同时进行而不会互相影响。select * from T where id1 lock in share mode写锁排它锁X锁(exclusive))当前写操作没有完成前它会阻断其他写锁和读锁。select * from T where id1 for update意向锁Intention LockI锁当有事务对一行数据加了x/s锁同时给表加一个标识意向锁。当其他事务要加表锁时不必逐行判断是否存在行锁与表锁冲突直接读取是否存在这个标识。当表中数据量多时直接通过判断是否存在意向锁提升加锁效率。意向排他锁IX锁加s锁之前需要先获取到意向共享锁意向共享锁IX锁加x锁之前需要先获取到意向排他锁从对数据操作的粒度区分表锁锁整张表加锁快开销小无死锁锁冲突最高并发度最低。使用场景数据迁移。命令手动增加表锁lock table 表名称 read(write),表名称2 read(write);查看表上加过的锁:show open tables;删除表锁:unlock tables;注意点读锁不堵塞其他进程读请求但会堵塞写请求写锁堵塞其他进程读写操作。同一张表读锁会阻塞写但是不会阻塞读。而写锁则会把读和写都阻塞行锁锁一行数据加锁慢开销大有死锁锁冲突概率低并发度最高InnoDB支持myisam不支持。行级锁允许大量并发读写操作只在操作同一行时才互相等待页锁锁一页数据开销介于表锁和行锁之间有死锁。锁定粒度介于表锁和行锁之间并发度一般。只有BDB引擎支持页锁。间隙锁Gap Lock锁两个值之间空隙只有在RR级别生效解决幻读问题。临键锁Next-key Locks行锁与间隙锁的组合。InnoDB与MYISAM的不同点最大不同有两点InnoDB支持事务TRANSACTION、支持行级锁。MYISAM不支持。不同点InnoDB默认行级锁表锁用于意向锁或手动加表锁场景select时是快照读不加锁update/insert/delete才加行锁写操作时操作同一行数据才会等待读操作不堵塞任何读写操作mvcc)。myisam默认表锁所有DML操作都会加表锁读前加读锁写前加写锁写操作会堵塞所有其他读取表或修改表的操作读操作不堵塞其他读操作堵塞所有写操作。InnoDB普通查询为快照读使用mvcc机制根本不需要加锁。更新操作自动添加排他锁只需要锁住修改的行。但是如果更新操作没有索引时无法确定锁住那些行会使用表锁。InnoDB的行锁是针对索引加的锁不是针对记录加的锁。并且该索引不能失效否则都会从行锁升级为表锁。MyISAM读操作会自动加上表级读锁。多个读操作可以同时进行不会互相阻塞。但一旦有写请求就必须等所有读锁都释放了才能执行。写操作 会自动加上表级写锁。会阻塞其他所有读写操作。对MyISAM表来说“写写互斥”和“读写互斥”是绝对的。锁等待如何分析检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况 命令show status like innodb_row_lock%; Innodb_row_lock_time_avg - 每次等待所花平均时间 Innodb_row_lock_waits - 系统启动后到现在 总共等待的次数 Innodb_row_lock_time - 从系统启动到现在锁定 总时间长度 Innodb_row_lock_time_max从系统启动到现在 等待最长的一次所花时间 Innodb_row_lock_current_waits: 当前正在等待锁定的数量查看INFORMATION_SCHEMA系统库锁相关数据表 ‐‐ 查看事务 select * from INFORMATION_SCHEMA.INNODB_TRX; ‐‐ 查看锁8.0之后需要换成这张表performance_schema.data_locks select * from INFORMATION_SCHEMA.INNODB_LOCKS; ‐‐ 查看锁等待8.0之后需要换成这张表performance_schema.data_lock_waits select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; ‐‐ 释放锁trx_mysql_thread_id可以从INNODB_TRX表里查看到 kill trx_mysql_thread_id; ‐‐ 查看锁等待详细信息 show engine innodb status; --查看近期死锁日志信息 show engine innodb status;锁优化方式尽可能让所有数据检索都通过索引来完成避免无索引行锁升级为表锁 合理设计索引尽量缩小锁的范围 尽可能减少检索条件范围避免间隙锁 尽量控制事务大小减少锁定资源量和时间长度涉及事务加锁的sql尽量放在事务最后执行 尽可能用低的事务隔离级别MVCC多版本并发控制机制undo日志版本链每条记录在每次修改后都会生成一个旧版本Undo 日志多个版本通过指针串联成链表.Undo 日志的结构trx_id 产生该版本的事务IDroll_pointer 指向上一个版本的指针实际数据 该版本的字段值Read View一致性读视图事务执行快照读时创建的一个数据可见性判断依据记录了当前系统中哪些事务是活跃的未提交组成执行查询时 所有“未提交事务id数组”数组里最小的id为min_id执行查询时 “已创建的最大事务id”max_id大致结构[min_idtrx_id1,trx_id2,…,trx_idn ]max_id对比规则沿着 Undo 版本链查找可见版本1、如果row的 trx_id 比 min_id 小则是这一行是已提交的数据是可见的。trx_id min_id2、如果row的 trx_id 比 max_id大则是这一行是未来启动的事务是不可见的。trx_id max_id。若 row 的 trx_id 就是当前自己的事务可见。3、如果row的 trx_id 大于等于min_id 并且 小于等于max_id (min_id trx_id max_id)3.1、若row的 trx_id在 “未提交事务id数组” 中则是这一行是未提交的事务生成的不可见。若 row 的 trx_id 就是当前自己的事务可见。3.2、若当前记录的 trx_id 不在 “未提交事务id数组” 中则这个事务已经提交可见。例如-- -- 初始数据假设最后提交事务 trx_id 50 版本记录1 id1, name张三, age20 trx_id50, roll_pointerNULLINSERTINTOusersVALUES(1,张三,20);COMMIT;-- 事务Atrx_id100BEGIN;UPDATEusersSETname李四WHEREid1;-- 版本记录2 id1, name李四, age20 trx_id100, roll_pointer -版本记录1-- 未提交-- 事务Btrx_id101BEGIN;UPDATEusersSETage25WHEREid1;-- 版本记录3 id1, name李四, age25 trx_id101, roll_pointer - 版本记录 2-- 未提交-- 事务Ctrx_id102当前查询事务BEGIN;-- 将在这里执行 SELECT 查询 查询结果 id1, name张三, age20SELECT*FROMusersWHEREid1;执行流程步骤1事务C 创建 Read View : [100, 101] 102 问题执行查询时已创建的最大事务id 是 未提交状态会不会放入未提交事务id数组中步骤2 沿着 Undo 版本链从新到旧依次判断当前记录版本记录3id1, name李四, age25 trx_id101, roll_pointer - 版本记录 2 ↓ 判断trx_id101 101 在 “未提交事务id数组” 中 → 不可见 查看上一个版本版本记录2 id1, name李四, age20 trx_id100, roll_pointer -版本记录1 ↓ 判断 trx_id100 101 在 “未提交事务id数组” 中 → 不可见 查看上一个版本版本记录1 id1, name张三, age20 trx_id50, roll_pointerNULL ↓ 判断 trx_id50 50 min_id → 可见Read View 就是记录执行sql查询时当前时刻的数据库未提交事务与提交事务状态。通过read-view机制与undo版本链对比不同的事务在版本链中读取不同版本数据。RR级别事务中每一次执行查询都是使用第一次查询当前时刻生成的read View视图与当前的undolog版本链比对。每次查询都会根据第一次查询时数据库所有事务状态判断是否可见。实现可重复读。RC级别事务中每一次执行查询都会重新生成当前时刻的read View试图与当前的undolog版本链比对。每次查询都会根据当时的数据库所有事务状态判断是否可见。每次查询最新数据。查询操作方法需要使用事务吗使用rc与rr进行查询数据时不同效果回答。