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

Today, a friend asked me about the LOCK of MYSQL. I sorted out an BLOG for everyone to understand and learn about his question. Interested students can refer to it to test and deepen their understanding of the principle.

Conclusion: 1.RR isolation level concurrency is not as good as RC 2. In the development process, the transaction should be as small as possible and the end should be quick. 3. Appropriate indexes need to be created to reduce the probability of full table scan.

The weird phenomenon of RR isolation level. RC isolation level is better than RR isolation level in concurrency.

1. The isolation level is RR. View as follows: mysql > show variables like'% iso%' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | REPEATABLE-READ | +-- + 1 row in set (0.00 sec) 2, Create a test table t_test4 and insert 4 records create table t_test4 (id int Name varchar (20)) INSERT INTO T_TEST4 VALUES (4). . Mysql > select * from tasking test 4; +-+-+ | id | name | +-+-+ | 4 | wuhan | 2 | zhej | 4 | zhej | 4 | zhej | 3. Open session 1. Execute the following statement. Since autocommit is enabled, use start transaction or begin to open a transaction to check whether autocommit is enabled: mysql > show variables like->'% auto%'. +-- +-- + | Variable_name | Value | +-- +-+ | auto_increment_increment | 1 | auto_increment_offset | 1 | autocommit | ON |-- automatic submission | automatic_sp_privileges | | ON | | innodb_autoextend_increment | 64 | innodb_autoinc_lock_mode | 1 | | innodb_stats_auto_recalc | ON | | sql_auto_is_null | OFF | +-- +-+ 8 rows in set (0.00 sec) |

Mysql > start transaction; Query OK, 0 rows affected (0.00 sec)

Mysql > update t_test4 set id=4 where name='wuhan';--- notice that the transaction is still not finished here Query OK, 0 rows affected (0 sec) Rows matched: 1 Changed: 0 Warnings: 0

4. Open session 2 and make an insert statement. Statement 2 is suspended until session 1 submits or waits for the INNODB timeout to be rolled back automatically. Check the INNODB timeout (default is 50 seconds here): mysql > show variables like'% timeout%' +-- +-+ | Variable_name | Value | +-+-+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | | innodb_lock_wait_timeout | 50 |-default 50 seconds | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | lock_wait_timeout | 31536000 | net_read_timeout | 30 | net_write_timeout | 60 | rpl_stop_slave_timeout | 31536000 | slave_net_timeout | 3600 | wait_timeout | 28800 | +-+ | -+ 12 rows in set (0.00 sec)

Mysql > insert into t_test4 values (4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

It is visible that the insert statement could not be executed successfully. In the test, let's look at information_schema.innodb_trx,information_schema.innodb_locks,information_schema.innodb_lock_waits separately.

Using lock check statements: it can be seen that 152session is blocked by 151session, INSERT INTO statement executed by 152session, lock statement currently executed by 151session

Mysql > SELECT-> r.trx_id waiting_trx_id,-> r.trx_mysql_thread_id waiting_thread,-> r.trx_query waiting_query,-> b.trx_id blocking_trx_id,-> b.trx_mysql_thread_id blocking_thread -> b.trx_query blocking_query-> 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 +- - - - -- + | waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | +- -+- - - -+ | 579724 | 152 | insert into t_test4 values (4) 'zhej') | 579720 | 151 | SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread B.trx_query blocking_query 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 |

The data is as follows: visible session 152, the insert statement is blocked, and the session 151 update statement locks five rows, but we only need to modify one.

Mysql > select * from information_schema.innodb_trx\ G * * 1. Row * * trx_id: 579737 trx_state: LOCK WAIT trx_started: 2017-09-02 01:29:12 trx_requested_lock_id: 579737 02 01:29:12 trx_weight: 2 trx_mysql_thread_id: 152 trx_query: insert into t_test4 values (4 'zhej') trx_operation_state: inserting trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 360 trx_rows_locked: 1 trx_rows_modified: 0 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. Row * * trx_id: 579733 trx_state: RUNNING trx_started: 2017-09-02 01:05:27 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql _ thread_id: 151trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 2 trx_lock_memory_bytes: 360trx_rows_locked: 5 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 At this point in the test, you should be able to find that the isolation level has poor concurrency in the case of RR, so what is the reason?

The principle is as follows: when the table does not make use of the upper secondary index or there is no index (my test is that there is no secondary index, when the scanned data is more than 20% of the table data, it may lead to a full table scan), MYSQL will do a full table scan, and at this time it will lock the whole table, that is, it will not be able to do any DML operation reference on the table. I only list the insert statement blocking here. If you are interested, you can see if DELETE and UPDATE are also blocked. In fact, it can be seen from above. (https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html) If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows . Other references are as follows: https://dev.mysql.com/doc/refman/5.6/en/where-optimization.html

The summary is as follows: 1. In the process of development, the transaction should be as small as possible and the end should be quick. 2. It is necessary to create a suitable index to reduce the probability of full table scan.

2. Modify the isolation level as follows (if you need to modify the my.cnf file for permanent modification) remember to exit the session and log in again after modification. Mysql > set global tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec)

3. Test and view the isolation level when the isolation level is RC: mysql > show global variables like'% iso%' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | READ-COMMITTED | +-+-+ 1 row In set (0.00 sec) mysql > show variables like'% iso%' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | READ-COMMITTED | +-+-+ 1 row in set (0.00 sec)

2. Session 1 executes SQL mysql > begin->; Query OK, 0 rows affected (0.00 sec)

Mysql > update t_test4 set id=5 where name='wuhan'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

3. Session 2 execution: there is no blocking mysql > insert into t_test4 values (4); Query OK, 1 row affected (0.00 sec)

You can observe that only one line mysql > select * from information_schema.innodb_trx\ G * * 1. Row * * trx_id: 579758 trx_state: RUNNING trx_started: 2017-09-02 02:33:29 trx_requested_lock_id: NULL trx is locked here at this time _ wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 155 trx_query: select * from information_schema.innodb_trx 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: 1 Murray-locked records trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED-- isolation level RC 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 1 row in set (0 sec)

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.