一、为什么需要锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
为了保证数据库中数据并发访问的一致性、有效性,故引入锁机制。其中锁冲突也是影响数据库并发访问性能的一个重要因素。
二、锁的分类
分类一:行锁,页锁,表锁;
分类二:共享锁、排他锁、意向锁(IS、IX);
分类三:乐观锁、悲观锁;
InnoDB的行级别锁分为:记录锁、间隔锁、next-key锁。
MySQL中,每种存储引擎支持不同的锁机制。
MyISAM、MEMORY:支持表级锁(table-level locking);
BDB:支持页面锁(table-level locking),也支持表级锁;
InnoDB:支持行级锁(row-level locking),也支持表级锁。
三、锁的优缺点
表级锁:开销小,加锁快;不会出现死锁;
锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢;会出现死锁;
锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
页面锁:开销和加锁时间界于表锁和行锁之间;
会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
四、锁的使用
1、查询MyISAM的锁的相关情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺。
show status like ‘table%’;
如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。
2、查询InnoDB的锁的相关信息
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:
mysql> show status like ‘innodb_row_lock%’;
3、查看数据库中的所有会话信息
show processlist;
4、查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
5、查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
6、手动锁表与释放
表级别锁:
共享锁(s锁/读锁)
LOCK TABLE table_name [ AS alias_name ] READ
排他锁(x锁/写锁 )
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE
释放锁:
unlock tables;
7、行级别锁表与释放(InnoDB引擎)
共享锁(s锁/读锁)
SELECT … LOCK IN SHARE MODE;
排他锁(x锁/写锁)
SELECT … FOR UPDATE;
释放锁:
unlock tables;
注意事项:
1、MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
2、InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!