|
********************************** 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。
********************************************************************************************** |