MySQL优化笔记其三

锁机制 MyISAM引擎

表锁

  • 查看加锁情况
show open tables;
  • 手动增加表锁
lock table 表名1 read(write),表名2 read(write),其他;
  • 手动释放锁
unlock tables;
  • read锁举例: session_1对dept表加read锁,seesion_1和session_2(泛指其他session)均可读dept,但是session_1无法对dept表写数据,也无法读取其他未锁定的表。session_2可以读取其他表,但是写数据进dept表被阻塞,等待获取锁。加read锁后,session_1无法写入数据到dept表以及读取其他表的数据。
  • write锁举例: session_1对dept加write锁,session_1无法读取以及写其他未加锁的表.session_2读取和写dept表时被阻塞。但是session_2可以读取其他表。
  • read锁是共享锁,write锁是独占锁。
  • 查看数据库加锁争用概况
show status like 'table%';

-- Table_locks_immediate: 产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
-- Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值越高说明存在着较严重的表级锁争用情况。
  • MyISAM引擎是写锁优先,因此,该引擎不适合做写为主表的引擎。因为加写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。

InnoDB引擎 行锁

  • 行锁支持事务
丢失更新(Lost Update): 多个事务操作同一行,每个事务不知道其他事务的存在,最后的事务更新覆盖了其他事务的更新。如果一个事务完成并提交之前,其他事务不能访问该行数据,则可避免此问题。
脏读(Dirty Reads): 事务B读到了事务A已修改但尚未提交的数据。此时如果事务A回滚,B读取到的数据无效,不符合一致性要求。设置读已提交(Read Committed)事务隔离级别可以避免此问题。
幻读(Phantom Reads): 一个事务按照相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足查询条件的新数据。不符合隔离性。
不可重复读(Non-Repeatable Reads): 一个事务在读取某些数据后的某个时间,再次读取以前读取过的数据,却发现其读出的数据已经发生了改变,或某些记录已经被删除了,这种现象称为"不可重复读"。
ACID:
  • MySQL默认的事务隔离级别为"可重复读"。
  • 事务的隔离级别
读数据的一致性脏读不可重复读幻读
未提交读(Read uncommitted)最低级别,只能保证不读取物理上损坏的数据
已提交读(Read committed)语句级
可重复读(Repeatable read)事务级
可序列化(Serializable)最高级别,事务级
  • 查看当前数据的事务隔离级别:
show variables like '%isolation%';
  • session_1更新后未提交,session_2对同一行的更新会被阻塞。
  • 索引失效导致行锁升级为表锁。例如:int和varchar自动隐式转换
  • 间隙锁的危害
用范围条件而不是相等条件检索数据,并请求共享或排它锁时,InnoDB会给符合条件的已有数据记录的索引项加锁,对于键值在条件范围内但并不存在的记录,叫做间隙(GAP)。InnoDB也会对这个间隙加锁,这种锁机制就是间隙锁。例如:范围a>1 and a<6,其中a不存在2这个值。

语句执行过程中通过范围查找用到行锁的话,InnoDB会锁定整个范围内所有的索引键值,即使这个键值不存在。
  • 锁定一行
select xxx[,xxx...] for update; 
  • 行锁分析
show status like 'innodb_row_lock%';
-- innodb_row_lock_current_waits: 当前正在等待锁定的数量
-- innodb_row_lock_time: 系统启动到现在锁定总时间长度
-- innodb_row_lock_time_max: 系统启动到现在等待最长的一次所花费的时间
-- innodb_row_lock_waits: 系统启动到现在总共等待的次数
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页