Shulou(Shulou.com)06/01 Report--

1. Lock

InnoDB supports row locks, sometimes upgraded to table locks.

MyISAM only supports table locks.

Table lock: open small, lock fast, there will be no deadlock; large lock granularity, high probability of lock conflict, low concurrency.

Row lock: high overhead, slow locking, deadlock, small lock granularity, low lock conflict probability and high concurrency.

1.1. InnoDB lock type

It is mainly divided into: read lock (shared lock), write lock (exclusive lock), intention lock, and MDL lock.

1.1.1. Read lock

Read lock, S lock, when a thing reads a row of data, other transactions can also read, but cannot add deletions to that data. The application of two ways of select.

The select query statement in auto-commit mode does not need to add any lock to return the result. It is a consistent unlocked read.

L add a read lock to the range of row records or row records that are read through select....lock in share mode, so that other transactions can read but not apply for write locks.

1.1.2. Write lock

Write lock is referred to as X lock. If a transaction acquires a write lock of a row, other transactions cannot acquire other locks of the row, and it has the highest priority.

Select for update, which adds a write lock to the read row record, and no other transaction can add any lock.

1.1.3. MDL lock

Mysql5.5 introduces meta data lock, or MDL lock for short, to protect the information of metadata in a table. That is, a transaction query table will automatically add MDL locks to the table, and other transactions cannot do any DDL operations.

1.1.4. Intention lock

In the InnoDB engine, the intention lock is a table-level lock, which is similar to MDL to prevent data inconsistency caused by the execution of DDL statements during the transaction. There are two types of intention locks:

Intention shared lock (IS): the IS lock of the table must be acquired before the data row can be added to the shared lock.

Intention exclusive lock (IX): the IX lock of the table must be acquired before the data row can be added to the exclusive lock.

1.2. InnoDB row lock type

The default transaction isolation level of InnoDB is RR, and in the mode of parameter innodb_locks_unsafe_for_binlog=0, there are three kinds of row locks.

The lock (record lock) of a single row record, both the primary key and the unique index.

L clearance lock (GAP lock)

The combination of record lock and gap lock is called next-key lock. Normal index defaults.

1.2.1. Lock for a single row record

The row lock on InnoDB is added to the index. There is an index, update locks only specified rows, no index, update locks all rows.

1.2.2. Gap lock (GAP lock)

RR isolation level, in order to avoid illusion, the introduction of Gap lock, only locks the range of row record data, does not include the record itself, that is, does not allow any data to be inserted in this range.

The RC isolation level allows for phantom reading.

1.2.3. Next-Key Locks

Next-key lock is a combination of record lock (Record Lock) and interval lock (Gap Lock). When InnoDB scans an index record, it will first add a record lock (Record lock) to the selected index record, and then add a gap lock (Gap lock) to the gap between the two sides of the index record.

1.3. Lock waiting and deadlock

Lock waiting is when one transaction generates a lock and other transactions wait for the last transaction to release its lock. Lock wait timeout threshold innodb_lok_wait_timeout control, in seconds.

Deadlock, the phenomenon that multiple transactions compete for resources and wait for each other, that is, the lock resource request produces a loop, which is a dead loop.

Ways to avoid deadlocks:

If different programs access multiple tables concurrently, or involve sweating records, try to agree to access the tables in the same order.

Try to use small transactions in the business, avoid large transactions, commit or rollback in time.

In the same transaction, lock all the resources needed at once as much as possible.

For businesses that are prone to deadlocks, you can try to use upgrade lock granularity to reduce the probability of locks through table locking.

View deadlock display information through show engine innodb sttus.

[(none)] > show engine innodb status

| | Type | Name | Status |

| | InnoDB |

= =

2018-11-07 22:49:40 0x7f1320202700 INNODB MONITOR OUTPUT

= =

Per second averages calculated from the last 11 seconds

-

BACKGROUND THREAD

-

Srv_master_thread loops: 98 srv_active, 0 srv_shutdown, 49465 srv_idle

Srv_master_thread log flush and writes: 49563

-

SEMAPHORES

-

OS WAIT ARRAY INFO: reservation count 20

OS WAIT ARRAY INFO: signal count 20

RW-shared spins 0, rounds 34, OS waits 16

RW-excl spins 0, rounds 200, OS waits 2

RW-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait: 34.00 RW-shared, 200.00 RW-excl, 0.00 RW-sx

-

TRANSACTIONS

-

Trx id counter 65440

Purge done for trx's n:o

< 65438 undo n:o < 0 state: running but idle History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421197684710112, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421197684709200, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) I/O thread 11 state: waiting for completed aio requests (write thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: waiting for completed aio requests (write thread) I/O thread 14 state: waiting for completed aio requests (write thread) I/O thread 15 state: waiting for completed aio requests (write thread) I/O thread 16 state: waiting for completed aio requests (write thread) I/O thread 17 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 271 OS file reads, 61118 OS file writes, 60451 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 1 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 20357616 Log flushed up to 20357616 Pages flushed up to 20357616 Last checkpoint at 20357607 0 pending log flushes, 0 pending chkp writes 60048 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2198863872 Dictionary memory allocated 156387 Buffer pool size 131056 Free buffers 130465 Database pages 590 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 238, created 352, written 805 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 590, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 16382 Free buffers 16279 Database pages 102 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 38, created 64, written 95 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 102, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 16382 Free buffers 16312 Database pages 70 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 6, created 64, written 64 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 70, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 16382 Free buffers 16319 Database pages 63 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 8, created 55, written 56 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 63, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 16382 Free buffers 16303 Database pages 79 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 73, created 6, written 59 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 79, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 16382 Free buffers 16265 Database pages 117 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 76, created 41, written 120 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 117, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 16382 Free buffers 16307 Database pages 75 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 11, created 64, written 91 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 75, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 16382 Free buffers 16363 Database pages 19 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 13, created 6, written 12 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 19, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 16382 Free buffers 16317 Database pages 65 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 13, created 52, written 308 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 65, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=21556, Main thread ID=139720374998784, state: sleeping Number of rows inserted 60824, updated 0, deleted 0, read 121836 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1.4. 锁问题的监控 通过show full processlist和show engine Innodb status来判断事务中锁问题情况，另外还有三张表可查： information_schema.INNODB_TRX information_schema.INNODB_LOCKS information_schema.INNODB_LOCK_WAITS innodb_trx表部分字段 trx_id唯一的事务id号； trx_state:事务状态； trx_wait_started:事务开始等待时间。 trx_mysql_thread_id:线程ID，与show full processlist相互对应。 trx_query:事务运行的SQL; trx_operation_state:事务运行的状态。 [(none)]>

Show full processlist

+-+-

| | Id | User | Host | db | Command | Time | State | Info |

+-+-

| | 57 | root | localhost | NULL | Query | 0 | starting | show full processlist | |

+-+-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.