MySQL锁的管理机制

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/48805847

**********************************

          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的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，
是会出现锁冲突的。应用设计的时侯要注意这点。