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

I. lock and latch

Latch, commonly known as latch, is used to ensure the correctness of critical resources for concurrent thread operations, and there is no deadlock detection mechanism. Divided into: mutex (mutex), rwlock (read-write lock)

The objects of lock are transactions that are used to lock objects in the database.

II. Lock species

1. Row level lock

Shared lock (S lock): allows a transaction to read a row of data

Exclusive lock (X lock): allows a transaction to modify or delete a row of data

two。 Table level lock

Intention shared lock (IS lock): appends an IS lock to a row's table before adding an S lock to it

Intention exclusive lock (IX lock): append an IX lock to a row's table before adding an X lock to it

Compatibility between table-level intention locks and row-level locks:

three。 Application scenario of lock

1. Consistent unlocked read

two。 Consistent lock read

Read data is locked through select * from table for update; or select * from table lock in share mode; and cannot be modified by other transactions during data reading.

3. Self-growth and lock

All tables with self-growing values have a self-growing counter. When inserting the table, execute the following statement to get the value of the counter.

Select max (auto_inc_col) from table for update

The lock is not released after the transaction is completed, but is released after the execution of the insert command.

Prior to mysql5.1.22, the schema had poor concurrent insert performance for tables with self-incrementing columns.

Since the beginning of mysql5.1.22, innodb has provided a lightweight self-growing mutex implementation mechanism, which greatly improves the insertion performance of self-growing values.

Related parameter, innodb_autoinc_lock_mode. Default is 1.

4. Foreign keys and locks

Under the innodb storage engine, if there is no explicit establishment of index,mysql for the foreign key column, index will be automatically added to the column to avoid table locking.

For inserts and updates of foreign key values, the parent table is select first, but the select operation is not a consistent unlocked read, but a consistent locked read (appending an S lock to the parent table). Therefore, when the parent table is X-locked by other transactions, the operation of the child table is blocked.

-- Master table

CREATE TABLE `www.j`.`t1` (

`deptno` INT NOT NULL

`deptname` VARCHAR (45) NOT NULL

`address` VARCHAR (45) NOT NULL

PRIMARY KEY (`deptno`))

-- subtable

CREATE TABLE `www.j`.`t2` (

`empno` INT NOT NULL

`empname` VARCHAR (45) NOT NULL

`age`INT NOT NULL

`deptno` INT NOT NULL

PRIMARY KEY (`empno`)

INDEX `deptno_ idx` (`deptno` ASC)

CONSTRAINT `deptno`

FOREIGN KEY (`deptno`)

REFERENCES `www.j`.`t1` (`deptno`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

Insert into wwj.t1 values (1) Beijing (1)

Insert into wwj.t1 values (2 recordings productmakers' Tianjin')

Insert into wwj.t1 values (3 recordings recording 'Shanghai')

Mysql > select * from information_schema.innodb_locks\ G * * 1. Row * * lock_id: 1303:26:3:2lock_trx_id: 1303lock_mode: Slock_type: RECORDlock_table: `www.j`.`t1`lock _ index: PRIMARYlock_space: 26lock_page: 3lock_rec: 2lock_data: 1* * 2. Row * * lock_id: 1298:26:3:2lock_trx_id: 1298lock_mode: Xlock_type: RECORDlock_table: `www.j`.`t1`lock _ index: PRIMARYlock_space: 26lock_page: 3lock_rec: 2lock_data: 12 rows in set 1 warning (0.00 sec)

four。 Algorithm of lock

Three algorithms for row locking:

1.record lock

Locks on a single record

2.gap lock

A gap lock that locks a range but does not include the record itself

3.next-key lock

Record lock+gap lock locks a range, but does not include the record itself

-- scene simulation

CREATE TABLE `www.j`.`t3` (

`idt3` INT NOT NULL

`idt4` INT NOT NULL

PRIMARY KEY (`idt3`)

INDEX `idx- 1` (`idt4` ASC))

Insert into wwj.t3 values (1) 10; insert into wwj.t3 values (3) 30); insert into wwj.t3 values (5) 50

Mysql > select * from wwj.t3;+-+-+ | idt3 | idt4 | +-+-+ | 1 | 10 | 3 | 30 | 5 | 50 | +-+-+ unique index lock range

Because there is a unique index on idt3, only the value of idt3=3 is locked, not the range (1p3), that is, the lock is downgraded from next-key lock to record lock

Lock range of secondary index

five。 Lock range of a sql

Analysis of locking in various cases

Mysql > show full processlist

+-+-

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

+-+-

| | 11 | root | localhost | NULL | Sleep | 99 | | NULL |

| | 12 | root | localhost | NULL | Sleep | 81 | | NULL |

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

+-+-

Kill the thread:

KILL [CONNECTION | QUERY] thread_id

Kill 13-kill thread connections

Kill query 13-kill the statement being executed and keep the connection

Mysql > show engine innodb status\ G

-

TRANSACTIONS

-

Trx id counter 1296

Purge done for trx's n:o

< 1294 undo n:o < 0 state: running but idle History list length 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421324408397424, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 1295, ACTIVE 396 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1 MySQL thread id 12, OS thread handle 139848225883904, query id 61 localhost root executing insert into wwj.t3 select 4,20 ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26 page no 4 n bits 72 index idx-1 of table `wwj`.`t3` trx id 1295 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000001e; asc ;; 1: len 4; hex 80000003; asc ;; --------------------- TRANSACTION 1294, ACTIVE 449 sec 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 11, OS thread handle 139848226150144, query id 50 localhost root Trx read view will not see trx with id >

= 1294, sees

< 1294 mysql>

Select * from information_schema.INNODB_LOCK_WAITS

+-+

| | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | |

+-+

| | 1302 | 1302 / 26 / 4 / 3 | 1301 | 1301 / 26 / 4 / 3 | |

+-+

1 row in set, 1 warning (0.00 sec)

Mysql > select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index from information_schema.INNODB_LOCKs

+-+ +

| | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | |

+-+ +

| | 1302Gap | 1302 | XMagneGAP | RECORD | `wwj`.`t3` | idx-1 |

| | 1301GRV 26GRV 4V3 | 1301 | X | RECORD | `wwj`.`t3` | idx-1 |

+-+ +

2 rows in set, 1 warning (0.00 sec)

Select p2.`HOST` Blockedhost, # blocked party host

P2.`USER`BlockedUser, # blocked party user

R.trx_id BlockedTrxId, # blocked party transaction id

R.trx_mysql_thread_id BlockedThreadId, # blocked program number

TIMESTAMPDIFF (

SECOND

R.trx_wait_started

CURRENT_TIMESTAMP

) WaitTime, # waiting time

R.trx_query BlockedQuery, # blocked query

L.lock_table BlockedTable, # tables locked by blocking parties

M.`lock _ Mode`BlockedLockMode, # the lock mode of the blocked party

M.`lock _ type`lock, # lock type of blocked party (table lock or row lock)

M. `lock _ index` BlockedLockIndex, # index locked by blocking party

M.`lock _ space` BlockedLockSpace, # the space_id of the blocked party lock object

M.lock_page BlockedLockPage, # number of blocked transaction locked pages

M.lock_rec BlockedLockRec, # number of blocked transaction locked rows

M.lock_data BlockedLockData, # the primary key value of the blocked transaction locked record

P.`HOST` blocking_host, # blocking party host

P.`USER`blocking_user, # blocking party user

B.trx_id BlockingTrxid, # blocking party transaction id

B.trx_mysql_thread_id BlockingThreadId, # blocking thread number

B.trx_query BlockingQuery, # blocking party query

L.`lock _ mode`BlockingLockMode, # lock mode of blocking party

L.`lock _ type`lock, # lock type of the blocking party (table lock or row lock)

L.`lock _ index` BlockingLockIndex, # index locked by blocking party

L.`lock _ space` BlockingLockSpace, # blocking the space_id of the object

L.lock_page BlockingLockPage, # number of transaction locked pages by blocking party

L.lock_rec BlockingLockRec, # number of blocked transaction locked rows

L.lock_data BlockingLockData, # the primary key value of the blocking party transaction lock record

IF (p.COMMAND = 'Sleep', CONCAT (p.Time ~' seconds'), 0) idel_in_trx # the idle time of the transaction by the blocker

FROM

Information_schema.INNODB_LOCK_WAITS w

INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock _ trx_ id` = b.`trx _ id`

INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock _ id` = w.`requested _ lock_ id`AND m.`lock _ trx_ id` = r.`trx _ id`

INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id

INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id

ORDER BY

WaitTime DESC

Reference books:

MySQL Technology Insider: InnoDB Storage engine

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.