Maison >base de données >tutoriel mysql >MySQL锁的管理机制_MySQL

MySQL锁的管理机制_MySQL

WBOY
WBOYoriginal
2016-05-30 17:11:001071parcourir

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

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

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn