分类:
从对数据操作的粒度分 :
1) 表锁:操作时,会锁定整个表。MyISAM默认。
2) 行锁:操作时,会锁定当前操作行。InnoDB默认。
从对数据操作的类型分:
1) 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
2) 写锁(排它锁):当前操作没有完成之前,它会阻断其他写锁和读锁。
MyISAM 如何 加/解 表锁?
MyISAM (加锁的过程一般不需要用户干预)
在执行 SELECT前,会自动给涉及的所有表加读锁。
在执行更新操作(UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁。
-- 加读锁 (table_name :表名):
lock table table_name read;
-- 加写锁 (table_name :表名):
lock table table_name write;
读锁会阻塞写,但是不会阻塞读。而写锁,则既会阻塞读,又会阻塞写。
-- 释放锁(读锁,解锁都可以)
unlock tables
查看MyISAM 锁的争用情况
-- 查看数据库的表
show open tables
In_user : 表当前被查询使用的次数(说明该表正在加锁)。如果该数为零,则表是打开的,但是当前没有被使用。
Name_locked:表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
-- 查看表的锁定情况
show status like 'Table_locks%'
Table_locks_immediate : 指的是能够立即获得表级锁的次数,每立即获取锁,值加1。
Table_locks_waited : 指的是不能立即获取表级锁而需要等待的次数,每等待一次,该值加1,此值高说明存在着较为严重的表级锁争用情况。
InnoDB 如何加锁?
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁;
-- 共享锁(S):
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
-- 排他锁(X) :
SELECT * FROM table_name WHERE ... FOR UPDATE
-- 关闭数据库事务自动提交
set autocommit = 0
InnoDB 虽然默认是行锁, 但是有时无索引行锁可以升级为表锁。
查看InnoDB 行锁争用情况
show status like 'innodb_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: 系统启动后到现在总共等待的次数
当等待的次数很高,而且每次等待的时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手制定优化计划。