每天进步一点点————事务控制（2）

来源：互联网发布：仙桃广电网络客服电话编辑：程序博客网时间：2024/04/27 23:42

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

请求锁模式

当前锁模式

冲突

兼容

冲突

兼容

冲突

兼容

冲突

兼容

如果一个事务请求的锁模式与当前的锁兼容，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锁。

0 0