1. 事务控制
如果在锁表期间,用start transaction开始事务,则会造成一个隐含的unlock tables
提交并开启一个新的事务
mysql>commit and chain;
Query OK, 0 rows affected (0.00 sec)
关闭的话就后面跟EXIT即可
查看数据库是否开启自动提交
mysql>show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
开启、关闭自动提交
mysql>set autocommit=on;
Query OK, 0 rows affected (0.00 sec)
2. 获取InnoDB行锁征用情况
通过InnoDB_row_lock状态变量来分析系统上的行锁征用情况
mysql>show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 209486 |
| Innodb_row_lock_time_avg | 41897 |
| Innodb_row_lock_time_max | 51048 |
| Innodb_row_lock_waits | 5 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
如果发现锁征用比较,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,可以通过查询information_schema数据库中相关的表来查看锁情况,或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。
通过information_schema数据库中的表了解解锁等待情况
mysql>select * from innodb_locks\G;
*************************** 1. row***************************
lock_id: 146943:236:3:203
lock_trx_id: 146943
lock_mode: X
lock_type: RECORD
lock_table: `sakila`.`actor`
lock_index: PRIMARY
lock_space: 236
lock_page: 3
lock_rec: 203
lock_data: 178
*************************** 2. row***************************
lock_id: 146942:236:3:203
lock_trx_id: 146942
lock_mode: S
lock_type: RECORD
lock_table: `sakila`.`actor`
lock_index: PRIMARY
lock_space: 236
lock_page: 3
lock_rec: 203
lock_data:178
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql>select * from INNODB_LOCK_waits\G;
*************************** 1. row***************************
requesting_trx_id: 146943
requested_lock_id: 146943:236:3:203
blocking_trx_id: 146942
blocking_lock_id: 146942:236:3:203
*************************** 2. row***************************
requesting_trx_id: 146943
requested_lock_id: 146943:236:3:203
blocking_trx_id: 146942
blocking_lock_id: 146942:236:3:203
2 rows in set (0.00 sec)
ERROR:
No query specified
通过设置InnoDB Monitors观察冲突情况。
mysql>create table innnodb_monitor(a int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)
mysql>show engine innodb status\G;
*************************** 1. row***************************
Type: InnoDB
Name:
Status:
=====================================
2015-08-25 11:11:31 7fb07b1c8700 INNODBMONITOR OUTPUT
=====================================
Per second averages calculated from thelast 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 151 srv_active, 0srv_shutdown, 67673 srv_idle
srv_master_thread log flush and writes:67824
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 705
OS WAIT ARRAY INFO: signal count 657
Mutex spin waits 596, rounds 17910, OSwaits 536
RW-shared spins 25, rounds 750, OS waits 18
RW-excl spins 1, rounds 4500, OS waits 148
Spin rounds per wait: 30.05 mutex, 30.00RW-shared, 4500.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 146757
Purge done for trx's n:o < 146756 undon:o < 0 state: running but idle
History list length 972
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 11, OS thread handle0x7fb07b1c8700, query id 1449341 localhost root init
show engine innodb status
---TRANSACTION 146508, not started
MySQL thread id 7, OS thread handle0x7fb07b24a700, query id 468 localhost root cleaning up
---TRANSACTION 146507, ACTIVE 169 secinserting
mysql tables in use 4, locked 4
17876 lock struct(s), heap size 2013624,2140040 row lock(s), undo log entries 2173133
MySQL thread id 8, OS thread handle0x7fb07b28b700, query id 1449342 localhost root update
insert into t88 values(new.id,'afterinsert')
--------
FILE I/O
--------
I/O thread 0 state: waiting for completedaio requests (insert buffer thread)
I/O thread 1 state: waiting for completedaio requests (log thread)
I/O thread 2 state: waiting for completedaio requests (read thread)
I/O thread 3 state: waiting for completedaio requests (read thread)
I/O thread 4 state: waiting for completedaio requests (read thread)
I/O thread 5 state: waiting for completedaio requests (read thread)
I/O thread 6 state: waiting for completedaio requests (write thread)
I/O thread 7 state: waiting for completedaio requests (write thread)
I/O thread 8 state: waiting for completedaio requests (write thread)
I/O thread 9 state: waiting for completedaio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] ,aio writes: 0 [0, 0, 0, 0] ,
ibufaio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; bufferpool: 0
6127 OS file reads, 17491 OS file writes,1152 OS fsyncs
27.49 reads/s, 16384 avg bytes/read, 131.22writes/s, 11.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 886, seg size888, 5 merges
merged operations:
insert 597, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 1 buffer(s)
35341.41 hash searches/s, 10538.62 non-hashsearches/s
---
LOG
---
Log sequence number 2061227564
Log flushed up to 2060967538
Pages flushed up to 2050661791
Last checkpoint at 2039088834
0 pending log writes, 0 pending chkp writes
438 log i/o's done, 5.75 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; inadditional pool allocated 0
Dictionary memory allocated 417085
Buffer pool size 8191
Free buffers 944
Database pages 7124
Old database pages 2609
Modified db pages 435
Pending reads 0
Pending writes: LRU 0, flush list 0, singlepage 0
Pages made young 298, not young 43445
4.75 youngs/s, 99.48 non-youngs/s
Pages read 6089, created 15682, written16478
27.49 reads/s, 110.22 creates/s, 121.72writes/s
Buffer pool hit rate 1000 / 1000,young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted withoutaccess 0.00/s, Random read ahead 0.00/s
LRU len: 7124, unzip_LRU len: 0
I/O sum[6796]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 2571, id140395908282112, state: sleeping
Number of rows inserted 2173187, updated 0,deleted 0, read 2818110
15228.44 inserts/s, 0.00 updates/s, 0.00deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.03 sec)
ERROR:
No query specified
3. InnoDB的行锁模式及加锁方法
InnoDB实现了以下两种类型的行锁
共享锁(S): 读取操作创建的锁。其他用户可以并发读取数据,但任何事务都不能获取数据上的排他锁,直到已释放所有共享锁。
排他锁(X):写入操作创建的锁。允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
为了实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(意向锁都是表锁)
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
请求锁模式 当前锁模式 | X | IX | S | IS |
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务,反之如果两者不兼容,该事务就要等待释放锁。
意向锁是InnoDB自动加的,不需要干预。对于update,delete和insert语句,InnoDB会自动发给涉及数据集加排他锁(X);对于普通select语句,InnoDB不会加任何锁。
事务可以通过以下语句显示给记录加共享锁和排他锁
加共享锁:select * from 表名 where ... lock in share model;
加排他锁: select * from 表名 where ... forupdate;
4. InnoDB行锁实现方式
InnoDB行锁是通过给缩影上的索引项加锁来实现的,如果没有索引,InnoDB将通过隐蔽的聚簇索引来对记录加锁。
record lock:对索引项加锁。
Gap lock:对索引项之间的“间隙”、第一条记录前的“间隙”或者最后一条记录后的“间隙”加锁。
Next-key lock:前两种的组合,对记录及其前面的间隙加锁。
InnoDB这种行锁实现的特点意味着:如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录进行加锁,实际效果跟表锁一样!
在实际应用中,要特别注意InnoDB行锁的这以特性,否则可以导致大量冲突,从而影响并发性能。
因为大量锁冲突导致并发性能下降例子
1、在不通过索引条件查询时,InnoDB会锁定表中所有记录
2、由于MySQL的行锁针对索引加锁,不针对记录加锁,所以虽然访问不通的记录,但是如果使用相同的索引会出现锁冲突
3、当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行。
4、即便在条件中使用了索引字段,但是MySQL使用全盘扫描方式扫描,这种情况InnoDB也会为所有记录加锁。
Next-Key锁
对于键值在范围条件内但是冰不存在的记录,叫做“间隙”(GAP),InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的Next-Key锁。