锁类型/引擎 | 行锁 | 表锁 | 页锁 |
---|---|---|---|
MyISAM | 有 | ||
InnoDB | 有 | 有 | |
BDB(被InnoDB取代) | 有 | 有 |
锁的适用场景
MyISAM表锁
MyISAM写阻塞读的例子 session 1 session 2 lock table user write; select * from user; //返回查询结果 select * from user; //被阻塞,等待锁被释放 unlock tables; 获得锁,返回查询结果 注:
MyISAM读阻塞写例子 session 1 session 2 lock table user read; 可查询:select * from user; 可查询:select * from user; 不能查询未锁定的表:select * from goods; //Table 'goods' was not locked with Lock Tables 能查询/更新未锁定的表 当前session更新锁定表会报错,Read Lock 更新锁定表会等待 Unlock tables; 获得锁,更新完成 MyISAM并发插入 系统变量 concurrent_insert:用于控制并发插入行为
MyISAM读写并发 session 1 session 2 lock table user read local; 当前session无法对该表更新或插入 可以插入,但更新需要等待锁释放 无法访问其他session插入的数据 unlock tables; 获得锁,更新完成 可以查到其他session插入的数据 注:
MyISAM锁调度
调节MyISAM锁调度行为
解决读写冲突的方法:
InnoDB与MyISAM最大区别:
事务 - Transaction 事务操作 描述 BEGIN 或者 START TRANSACTION 开始事务 COMMIT 提交事务 ROLLBACK 回滚结束事务,撤销进行中的所有未提交的修改 SAVEPOINT identifier 设置保存点 RELEASE SAVEPOINT identifier 事务回滚到保存点 ROLLBACK TO identifier 撤销保存点 SET TRANSACTION = {READ UNCOMMITED,READ COMMITED,REPEATABLE READ,SERIALIZABLE} 设置事务隔离级别 SET AUTOCOMMIT = {0,1} 禁止/开启自动提交 事务的特性
并发事务问题 描述 解决方案 更新丢失 两个事务对同一行数据修改,先提交的被后提交的覆盖 应用程序对要更新的数据加锁 脏读 A事务改一行数据,B事务读到了A的改动“脏”数据,A回滚则B的数据有问题 数据库事务隔离,解决读一致性问题:1、读之前加锁,防止其他事务对数据修改;2、不加锁,生成快照,多版本并发控制 不可重复读 一个事务多次读取同一数据发现被改变/删除 同上 幻读 一个事务按先前的条件查询,发现其他事务插入了满足条件的新数据 同上 注: 事务隔离级别越高,并发副作用越小,代价越高,因为事务隔离从某种程度上说使得事务串行化。 MySQL事务隔离级别 隔离级别/并发问题 读一致性 脏读 不可重复读 幻读 未提交读 最低 有 有 有 已提交读 语句级 无 有 有 可重复读 事务级 无 无 有 可序列化 最高 无 无 无 获取InnoDB行锁争用情况
InnoDB行锁类型 行锁类型 描述 共享锁 S 允许事务读一行,阻止其他事务获得排他锁 排他锁 X 允许事务更新数据,阻止其他事务获得共享读锁和排他写锁 意向共享锁 IS 事务打算给行加共享锁,先取得表IS锁 意向排他锁 IX 事务打算给行加排他锁,先取得表IX锁 请求锁模式是否兼容当前锁模式 X IX S IS X 否 否 否 否 IX 否 是 否 是 S 否 否 是 是 IS 否 是 是 是 注:
手动加锁的方法
注:
间隙锁(Next-key Lock) 概念描述 用范围而非等值搜索数据,并且请求共享/排他锁时,InnoDB会对所有符合条件的已有记录的索引项加锁,对键值在范围内但不存在的记录,即GAP-间隙,也会加锁。 例如:
作用:
存在的问题: 按范围加锁机制会阻塞符合条件范围内的键值并发插入,造成锁等待。 解决方法: 优化业务逻辑,尽量用相等条件来检索数据。 注: 相等条件检索一个不存在记录加锁时,InnoDB也会使用间隙锁。例如:
user
(id
, name
, password
, description
)
VALUES
(101, 'clive', '123456', 'psw'); //查询被阻塞,进入等待直至锁释放死锁的概念 死锁是指多个事务在统一资源上,出现相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。 MyISAM和InnoDB在死锁上的区别