Shulou(Shulou.com)05/31 Report--

This article mainly explains "the application of next-lock lock in MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the application of next-lock lock in MySQL".

Conclusion:

A. Mysql locks a range of records according to the index of the where condition of the update statement due to the existence of the next-lock lock.

If the index does not exist, the record of the entire table is locked. If the condition is primary key, it becomes a row lock.

B. next-lock is to avoid fantasy reading, so this happens by default in the case of repeated read.

c. For the isolation level of read commited, next-lock locks are not used, but row locks are used, which is also the way of oracle

In this case, concurrency support is better than repeated read.

1. Set the isolation level at the session level to repeatable

Set tx_isolation='repeatable-read'

two。 Check: the session level has been modified, but the global level has not changed.

Root@test 12:11:56 > show variables like'% iso%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

Root@test 12:12:06 > show global variables like'% iso%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | READ-COMMITTED |

+-+ +

3. Open a transaction in the session to update a row of records in the table

Root@test 12:19:12 > begin

Query OK, 0 rows affected (0.00 sec)

Since there is no index in this table, even if there is only one updated row, all records are actually locked due to the existence of next-lock.

Root@test 12:19:20 > update test set name='aa11' where id=1

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

4. Authentication: open another session, try to update another piece of data, and find that you wait for the lock until a timeout error is reported.

Root@test 12:19:39 > update test set name='bb11' where id=2

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Insert also waits because it has a lock until an error is reported.

Root@test 12:20:02 > insert into test values (3memery cc')

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

5. Check the lock information: only the transaction information, not the lock information. There is no lock information even under the isolation level of read-committed.

Select * from innodb_trx\ G

* 2. Row * *

Trx_id: 151933845

Trx_state: RUNNING

Trx_started: 2016-08-18 12:19:33

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 3

Trx_mysql_thread_id: 31

Trx_query: NULL

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 0

Trx_lock_structs: 2

Trx_lock_memory_bytes: 360

Trx_rows_locked: 3

Trx_rows_modified: 1

Trx_concurrency_tickets: 0

Trx_isolation_level: REPEATABLE READ

Trx_unique_checks: 1

Trx_foreign_key_checks: 1

Trx_last_foreign_key_error: NULL

Trx_adaptive_hash_latched: 0

Trx_adaptive_hash_timeout: 10000

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

2 rows in set (0.00 sec)

Root@information_schema 12:31:26 > select * from INNODB_LOCKS

Empty set (0.00 sec)

The status information Innodb_current_row_locks can display the lock information.

Root@test 01:43:13 > show status like'% lock%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_lock_tables | 0 | |

| | Com_lock_tables_for_backup | 0 | |

| | Com_lock_binlog_for_backup | 0 | |

| | Com_show_slave_status_nolock | 0 | |

| | Com_unlock_binlog | 0 | |

| | Com_unlock_tables | 0 | |

| | Handler_external_lock | 2 | |

| | Innodb_deadlocks | 0 | |

| | Innodb_row_lock_current_waits | 0 | |

| | Innodb_current_row_locks | 2 | |

| | Innodb_row_lock_time | 12002 | |

| | Innodb_row_lock_time_avg | 6001 | |

| | Innodb_row_lock_time_max | 6001 | |

| | Innodb_row_lock_waits | 2 | |

| | Innodb_s_lock_os_waits | 100 | |

| | Innodb_s_lock_spin_rounds | 3455 | |

| | Innodb_s_lock_spin_waits | 1550 | |

| | Innodb_x_lock_os_waits | 48 |

| | Innodb_x_lock_spin_rounds | 5238 | |

| | Innodb_x_lock_spin_waits | 1539 | |

| | Key_blocks_not_flushed | 0 | |

| | Key_blocks_unused | 53578 | |

| | Key_blocks_used | 7 | |

| | Performance_schema_locker_lost | 0 | |

| | Performance_schema_rwlock_classes_lost | 0 | |

| | Performance_schema_rwlock_instances_lost | 0 | |

| | Qcache_free_blocks | 1 | |

| | Qcache_total_blocks | 1 | |

| | Table_locks_immediate | 126 |

| | Table_locks_waited | 0 | |

+-+ +

30 rows in set (0.00 sec)

6. Further verification: the data of the INNODB_LOCKS table will be displayed only during the execution of the statement waiting for the lock, and before the Lock wait timeout, which is inconsistent with the view information of Oracle's v$locked_object.

This view shows which sessions are currently locked on which objects.

Root@information_schema 02:44:02 > select * from INNODB_LOCKS\ G

* * 1. Row *

Lock_id: 151933860:250:3:1

Lock_trx_id: 151933860

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`test`

Lock_index: GEN_CLUST_INDEX

Lock_space: 250

Lock_page: 3

Lock_rec: 1

Lock_data: supremum pseudo-record

* 2. Row * *

Lock_id: 151933845:250:3:1

Lock_trx_id: 151933845

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`test`

Lock_index: GEN_CLUST_INDEX

Lock_space: 250

Lock_page: 3

Lock_rec: 1

Lock_data: supremum pseudo-record

2 rows in set (0.00 sec)

-- you can also see that you are waiting for that lock, that session.

Root@information_schema 04:41:40 > select * from INNODB_LOCK_WAITS\ G

* * 1. Row *

Requesting_trx_id: 151933866 (transaction ID requesting lock) e

Requested_lock_id: 151933866:250:3:5

Blocking_trx_id: 151933845 (transaction ID for blocking locks)

Blocking_lock_id: 151933845:250:3:5

1 row in set (0.00 sec)

7. Summary if a statement times out, to determine which thread and which statement locked the data, you can use the following sql

Select a.lock_table,a.lock_type,a.lock_space,c.trx_mysql_thread_id

D.Command,d.Info, d.State,d.Time, d.Rows_sent, d.Rows_examined

From INNODB_LOCKS a, INNODB_LOCK_WAITS b, innodb_trx c, processlist d

Where a.lock_trx_id=b.blocking_trx_id

And a.lock_trx_id=c.trx_id

And c.trx_mysql_thread_id=d.id

+-+

| | lock_table | lock_type | lock_space | trx_mysql_thread_id | Command | Info | State | Time | Rows_sent | Rows_examined |

+-+

| | `test`.`test` | RECORD | 250 | 31 | Sleep | NULL | | 10848 | 2 | 2 |

+-+

1 row in set (0.00 sec)

Thank you for your reading, the above is the content of "the application of next-lock lock in MySQL". After the study of this article, I believe you have a deeper understanding of the application of next-lock lock in MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.