MySQL锁的管理机制_MySQL
程序员文章站
2022-04-23 22:58:08
...
**********************************
MySQL锁的管理机制
? table-level locking（表级锁）
? page-level locking（页级锁）
? row-level locking（行级锁）
————————————————————————————————————————————————————————————————————
一、表级锁：直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许.
对MyISAM表进行表级锁定
MyISAM表的锁
? 读锁，LOCK TABLE GYJ_T1 READ，自身只读，不能写；其他线程仍可读，不能写。多个线程都可提交read lock。
? 写锁，LOCK TABLE GYJ_T1 [LOW_PRIORITY] WRITE ，自身可读写；其他线程完全不可读写。
? 释放锁，UNLOCK TABLES
? SELECT自动加读锁
? 其他DML、DDL自动加写锁
Innodb行级锁升级表级锁的三种情况。
1.Innodb auto-inc锁
InnoDB处理具有auto increment字段的表的时候，会使用一种特殊的表锁——AUTO-INC。
简单来说就是innodb会在内存里保存一个计数器用来记录auto_increment的值，当插入数据时，就会用一个表锁来锁住这个计数器，
直到插入结束。一条一条插入问题不大，但是如果高并发插入，就会造成sql阻塞。
解决方法有两种
A)不用auto increment字段，自己维护主键生成。该方法中选择主键生成策略很重要, 要综合考虑简单和效率问题。假设使用uuid，
虽然简单但是会造成该表的主键效率很低（innodb的主键是特殊的index，其他的index会引用主键，详见mysql文档）
B) 修改innodb_autoinc_lock_mode
innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode：可预判行数时使用新方式，不可时使用表锁)
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不适合replication)
2.Innodb 全表更新、全索引更新
3.Innodb 使用SR事务隔离级别
二、页级锁:表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
对BDB表进行页级锁定，BDB现在没有了，很老的数据库，4点几的才有，现在从数据库上删除掉了
三、行级锁:仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
对InnoDB表进行行级锁定
Innodb加行锁的方式
1.record lock（行/记录锁）
2.gap lock（间隙锁）
3.next-key lock （record lock + gap lock）
InnoDB是通过给索引上的索引项加锁来实现行锁
InnoDB有几种锁：
? 共享锁（S - LOCKING），允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
? 排它锁（X - LOCKING），允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他锁
InnoDB还独有的实现了2种锁：
? 意向共享锁（IS），事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
? 意向独占锁（IX），事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁
注意：
（1）在不通过索引条件查询的时候，InnoDB使用的是表锁（默认地，全表所有行加锁，和表级锁相当，
例外条件是 RC + innodb_locks_unsafe_for_binlog 组合选项），而不是细粒度行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，
是会出现锁冲突的。
共享锁：SELECT * FROM xx WHERE … LOCK IN SHARE MODE
加排他锁：SELECT * FROM xx WHERE … FOR UPDATE
在5.1以前，只能通过SHOW FULL PROCESSLIST、SHOW ENGINE INOODB STATUS等命令查看锁的状态
在5.1之后：(使用了InnoDB plugin之后)
INFORMATION_SCHEMA：
INNODB_TRX
INNODB_LOCKS
InnoDB_LOCK_WAITS
show engine innodb mutex; #latch锁
show engine innodb status\G; #lock锁
INNODB_TRX
select * from information_schema.innodb_trx\G;
INNODB_LOCKS
select * from information_schema.innodb_locks\G; |
INNODB_LOCK_WAITS
select * from information_schema.innodb_lock_waits\G;
innodb_trx:
看下innodb_trx表中，几个最常用的字段：
trx_id:InnoDB存储引擎内部唯一的事务ID
trx_state:当前事务的状态
trx_started:事务的开始时间。
trx_wait_started:事务等待开始的时间。
trx_mysql_thread_id:Mysql中的线程ID,SHOW PROCESSLIST显示的结果。
trx_query：事务运行的sql语句。
innodb_locks
看下innodb_locks表中，几个最常用的字段：
lock_id:锁的ID。
lock_trx_id:事务ID。
lock_mode:锁的模式。
lock_type:锁的类型，表锁还是行锁。
lock_table:要加锁的表。
lock_index:锁的索引。
lock_space:InnoDB存储引擎表空间的ID号。
lock_page:被锁住的页的数量。若是表锁，则该值为NULL。
lock_rec:被锁住的行的数量。若是表锁，则该值为NULL。
lock_data:被锁住的行的主键值。当是表锁时，该值为NULL。
innodb_lock_waits
看下innodb_lock_waits表中，几个最常用的字段：
requesting_trx_id:申请锁资源的事务ID。
requesting_lock_id:申请的锁的ID。
blocking_trx_id:阻塞的锁的ID。
***************************************************************************************************************
实验一：观察INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS、processlist,status
**************************************************************************************************************
create table gyj_t1(id int primairy key,name varchar(10));
insert into gyj_t1 values(1,'AAAAA');
mysql> show variables like '%autocommit%';
mysql> select @@tx_isolation;
mysql> show variables like 'innodb_lock_wait_timeout';
mysql> set global innodb_lock_wait_timeout=600;
mysql> set innodb_lock_wait_timeout=600;
session 1
mysql> begin;
mysql> update gyj_t1 set name='BBBBB' where id=1;
session 2
mysql> begin;
mysql> update gyj_t1 set name='bbbbb' where id=1;
session 3
mysql> select * from information_schema.innodb_trx\G;
mysql> select * from information_schema.innodb_locks\G;
mysql> select * from information_schema.innodb_lock_waits\G;
mysql> show processlist;
mysql> show engine innodb status\G;
*********************************************
实验二：锁案例一，聚集索引上的锁
**********************************************
1.默认RR隔离级别
2.自动提交
3.创建表
CREATE TABLE student
(
id int unsigned not null auto_increment,
xh int unsigned not null,
name varchar(10) not null,
bjmc varchar(20) not null,
primary key(id),
key xh(xh)
) engine =InnoDB;
3.插入两条记录
insert into student values (1, 1, 'guoyj', 'jsj01'), (2, 2, 'jfedu', 'jsj01');
4.场景一
set autocommit=0;
(1)session 1
select * from student where id=1 for update;
(2)session 2
select * from student where id=1; #一致性非锁定读，这时侯会阻塞吗？（不会）
select * from student where id=1 lock in share mode; #这时侯会阻塞吗？（会）
(3)session 1
commit;或 rollback;
总结：一致性非锁定读测试（不产生任何锁，所以不会锁等待）
意向排它锁，意向共享锁互斥测试（会发生锁等待）
5.场景二
set autocommit=0;
(1)session 1
select * from student where name='guoyj' for update;
(2)session 2
select * from student where name='jfedu' for update; #这时侯会阻塞吗？(会)
(3)session 1
commit;或 rollback;
总结：看表结构，name这列没有索引，在RR隔离级别所有的记录全部都会被锁定，排它锁。
6.场景三
set autocommit=0;
(1)session 1
select * from student where xh=1 and name='guoyj' for update;
(2)session 2
select * from student where xh=1 and name='jfedu' for update; #这时侯会阻塞吗？(会)
(3)session 1
commit;或 rollback;
总结：xh是有索引的，xh=1，会话1会话2是同一行记录，同一个索引会被锁定的，出现冲突，发生等（name上没有索引，范围会扩大！）
7.场景四
那如果我把会话1的SQL，换成：select *from student where xh=2 and name='jfedu' for update;后会话2会发生锁等待吗？
set autocommit=0;
(1)session 1
select * from student where xh=1 and name='guoyj' for update;
(2)session 2
select * from student where xh=2 and name='jfedu' for update; #这时侯会阻塞吗？(不会)
总结：
会话2：xh是有索引的，xh=2 会话1会话2是不同的行记录，不是同一个索引，不会发生等待！
MySQL的行锁是针对索引加的锁，而不是记录加的锁！
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，
是会出现锁冲突的。应用设计的时侯要注意这点。
**********************************
MySQL server层面的一些锁
? table-level locking（表级锁）
? page-level locking（页级锁）
? row-level locking（行级锁）
————————————————————————————————————————————————————————————————————
一、表级锁：直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许.
对MyISAM表进行表级锁定
MyISAM表的锁
? 读锁，LOCK TABLE GYJ_T1 READ，自身只读，不能写；其他线程仍可读，不能写。多个线程都可提交read lock。
? 写锁，LOCK TABLE GYJ_T1 [LOW_PRIORITY] WRITE ，自身可读写；其他线程完全不可读写。
? 释放锁，UNLOCK TABLES
? SELECT自动加读锁
? 其他DML、DDL自动加写锁
Innodb行级锁升级表级锁的三种情况。
1.Innodb auto-inc锁
InnoDB处理具有auto increment字段的表的时候，会使用一种特殊的表锁——AUTO-INC。
简单来说就是innodb会在内存里保存一个计数器用来记录auto_increment的值，当插入数据时，就会用一个表锁来锁住这个计数器，
直到插入结束。一条一条插入问题不大，但是如果高并发插入，就会造成sql阻塞。
解决方法有两种
A)不用auto increment字段，自己维护主键生成。该方法中选择主键生成策略很重要, 要综合考虑简单和效率问题。假设使用uuid，
虽然简单但是会造成该表的主键效率很低（innodb的主键是特殊的index，其他的index会引用主键，详见mysql文档）
B) 修改innodb_autoinc_lock_mode
innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode：可预判行数时使用新方式，不可时使用表锁)
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不适合replication)
2.Innodb 全表更新、全索引更新
3.Innodb 使用SR事务隔离级别
二、页级锁:表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
对BDB表进行页级锁定，BDB现在没有了，很老的数据库，4点几的才有，现在从数据库上删除掉了
三、行级锁:仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
对InnoDB表进行行级锁定
Innodb加行锁的方式
1.record lock（行/记录锁）
2.gap lock（间隙锁）
3.next-key lock （record lock + gap lock）
InnoDB是通过给索引上的索引项加锁来实现行锁
InnoDB有几种锁：
? 共享锁（S - LOCKING），允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
? 排它锁（X - LOCKING），允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他锁
InnoDB还独有的实现了2种锁：
? 意向共享锁（IS），事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
? 意向独占锁（IX），事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁
注意：
（1）在不通过索引条件查询的时候，InnoDB使用的是表锁（默认地，全表所有行加锁，和表级锁相当，
例外条件是 RC + innodb_locks_unsafe_for_binlog 组合选项），而不是细粒度行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，
是会出现锁冲突的。
共享锁：SELECT * FROM xx WHERE … LOCK IN SHARE MODE
加排他锁：SELECT * FROM xx WHERE … FOR UPDATE
在5.1以前，只能通过SHOW FULL PROCESSLIST、SHOW ENGINE INOODB STATUS等命令查看锁的状态
在5.1之后：(使用了InnoDB plugin之后)
INFORMATION_SCHEMA：
INNODB_TRX
INNODB_LOCKS
InnoDB_LOCK_WAITS
show engine innodb mutex; #latch锁
show engine innodb status\G; #lock锁
INNODB_TRX
select * from information_schema.innodb_trx\G;
INNODB_LOCKS
select * from information_schema.innodb_locks\G; |
INNODB_LOCK_WAITS
select * from information_schema.innodb_lock_waits\G;
innodb_trx:
看下innodb_trx表中，几个最常用的字段：
trx_id:InnoDB存储引擎内部唯一的事务ID
trx_state:当前事务的状态
trx_started:事务的开始时间。
trx_wait_started:事务等待开始的时间。
trx_mysql_thread_id:Mysql中的线程ID,SHOW PROCESSLIST显示的结果。
trx_query：事务运行的sql语句。
innodb_locks
看下innodb_locks表中，几个最常用的字段：
lock_id:锁的ID。
lock_trx_id:事务ID。
lock_mode:锁的模式。
lock_type:锁的类型，表锁还是行锁。
lock_table:要加锁的表。
lock_index:锁的索引。
lock_space:InnoDB存储引擎表空间的ID号。
lock_page:被锁住的页的数量。若是表锁，则该值为NULL。
lock_rec:被锁住的行的数量。若是表锁，则该值为NULL。
lock_data:被锁住的行的主键值。当是表锁时，该值为NULL。
innodb_lock_waits
看下innodb_lock_waits表中，几个最常用的字段：
requesting_trx_id:申请锁资源的事务ID。
requesting_lock_id:申请的锁的ID。
blocking_trx_id:阻塞的锁的ID。
***************************************************************************************************************
实验一：观察INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS、processlist,status
**************************************************************************************************************
create table gyj_t1(id int primairy key,name varchar(10));
insert into gyj_t1 values(1,'AAAAA');
mysql> show variables like '%autocommit%';
mysql> select @@tx_isolation;
mysql> show variables like 'innodb_lock_wait_timeout';
mysql> set global innodb_lock_wait_timeout=600;
mysql> set innodb_lock_wait_timeout=600;
session 1
mysql> begin;
mysql> update gyj_t1 set name='BBBBB' where id=1;
session 2
mysql> begin;
mysql> update gyj_t1 set name='bbbbb' where id=1;
session 3
mysql> select * from information_schema.innodb_trx\G;
mysql> select * from information_schema.innodb_locks\G;
mysql> select * from information_schema.innodb_lock_waits\G;
mysql> show processlist;
mysql> show engine innodb status\G;
*********************************************
实验二：锁案例一，聚集索引上的锁
**********************************************
1.默认RR隔离级别
2.自动提交
3.创建表
CREATE TABLE student
(
id int unsigned not null auto_increment,
xh int unsigned not null,
name varchar(10) not null,
bjmc varchar(20) not null,
primary key(id),
key xh(xh)
) engine =InnoDB;
3.插入两条记录
insert into student values (1, 1, 'guoyj', 'jsj01'), (2, 2, 'jfedu', 'jsj01');
4.场景一
set autocommit=0;
(1)session 1
select * from student where id=1 for update;
(2)session 2
select * from student where id=1; #一致性非锁定读，这时侯会阻塞吗？（不会）
select * from student where id=1 lock in share mode; #这时侯会阻塞吗？（会）
(3)session 1
commit;或 rollback;
总结：一致性非锁定读测试（不产生任何锁，所以不会锁等待）
意向排它锁，意向共享锁互斥测试（会发生锁等待）
5.场景二
set autocommit=0;
(1)session 1
select * from student where name='guoyj' for update;
(2)session 2
select * from student where name='jfedu' for update; #这时侯会阻塞吗？(会)
(3)session 1
commit;或 rollback;
总结：看表结构，name这列没有索引，在RR隔离级别所有的记录全部都会被锁定，排它锁。
6.场景三
set autocommit=0;
(1)session 1
select * from student where xh=1 and name='guoyj' for update;
(2)session 2
select * from student where xh=1 and name='jfedu' for update; #这时侯会阻塞吗？(会)
(3)session 1
commit;或 rollback;
总结：xh是有索引的，xh=1，会话1会话2是同一行记录，同一个索引会被锁定的，出现冲突，发生等（name上没有索引，范围会扩大！）
7.场景四
那如果我把会话1的SQL，换成：select *from student where xh=2 and name='jfedu' for update;后会话2会发生锁等待吗？
set autocommit=0;
(1)session 1
select * from student where xh=1 and name='guoyj' for update;
(2)session 2
select * from student where xh=2 and name='jfedu' for update; #这时侯会阻塞吗？(不会)
总结：
会话2：xh是有索引的，xh=2 会话1会话2是不同的行记录，不是同一个索引，不会发生等待！
MySQL的行锁是针对索引加的锁，而不是记录加的锁！
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，
是会出现锁冲突的。应用设计的时侯要注意这点。