A little progress every day ———— transaction control (2)

1. Transaction control

If you start a transaction with the start transaction during the lock table, you will create an implied unlock tables commit and open a new transaction

Mysql>commit and chain;

Query OK, 0 rows Affected (0.00 sec)

Close with exit to see if the database turned on autocommit

mysql>show variables like '%autocommit% ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| autocommit | Off |

+---------------+-------+

1 row in Set (0.00 sec) turn on and off Autocommit

Mysql>set Autocommit=on;

Query OK, 0 rows Affected (0.00 sec) 2. Get InnoDB row Lock requisition case analysis of row lock requisition on system by innodb_row_lock state variable

 

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)

If you find a lock requisition comparison, such as Innodb_row_lock_waits and Innodb_row_lock_time_avg, you can view the lock by querying the related tables in the INFORMATION_SCHEMA database. or by setting InnoDB monitors to further observe the table, data row, and so on, and analyze the reason for lock contention. Understanding the unlock wait situation by using tables in the INFORMATION_SCHEMA database

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 The conflict situation by setting 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, 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 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, 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, no 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 mode and lock method of the line lock

InnoDB implements the following two types of row locks

Shared Lock (S): The lock created by the read operation. Other users can read data concurrently, but no transaction can get exclusive locks on the data until all shared locks have been freed.

Exclusive Lock (X): The lock created by the write operation. Allows transactional update data with exclusive locks to prevent other transactions from acquiring shared read locks and exclusive write locks for the same dataset.

in order to realize the multiple granularity lock mechanism, INNODB also has two kinds of intent locks which are used internally (intent locks are table locks).

Intent shared Lock (IS): a transaction intends to add rows to a data row to share a lock, which must be locked before a transaction can share a lock on a data row.

Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must first obtain the IX lock of the data row before adding an exclusive lock to it.

Request Lock mode Current lock mode	X	Ix	S	Is
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
Is	Conflict	Compatible	Compatible	Compatible

If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction, whereas if the two are incompatible, the transaction waits for the lock to be released.

The intent lock is innodb automatically and does not require intervention. For Update,delete and INSERT statements, InnoDB automatically sends an exclusive lock (X) to the dataset, and InnoDB does not add any locks to the normal SELECT statement.

transactions can be displayed to records with shared and exclusive locks through the following statements

Add shared Lock: SELECT * FROM table name where ... lock in share model;

Add exclusive Lock: SELECT * FROM table name where ... forupdate; 4. InnoDB line Lock Implementation mode

The InnoDB row lock is achieved by locking the index entry on the miniature, and if there is no index, the InnoDB will lock the record with a hidden clustered index.

Record Lock: locks the index entry.

Gap Lock: "gap" between index entries, "gap" before the first record, or "gap" after the last record.

Next-key Lock: The first two combinations, which lock the records and the gaps in front of them.

InnoDB this type of line lock implementation means that if you do not retrieve data by index criteria, InnoDB will lock all the records in the table with the actual effect of the table lock.

in practical applications, special attention should be paid to this feature of InnoDB row locks, which can cause a lot of conflicts and thus affect concurrency performance. Example of concurrent performance degradation due to a large number of lock conflicts

1, when not through the index criteria query, INNODB will lock all records in the table

2, because the MySQL row lock for the index lock, not for the record lock, so although access to the record, but if using the same index will have a lock conflict

3. When a table has multiple indexes, different transactions can lock different rows with different indexes.

4, even if the index field is used in the condition, but the MySQL uses the whole scan method to scan, this situation InnoDB also will lock for all records. Next-key Lock

For the key values in the scope of the condition but the ice does not exist records, called "Gap" (GAP), InnoDB will also add to the "gap" lock, the locking mechanism is called Next-key lock.