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

Continue to compare the differences between RC and RR isolation levels in the previous article, with a comparison of the appropriate index:

1. The isolation level is RR. Add the appropriate index on the t _ test4 table, that is, name column add secondary index session 158. view the isolation level and create an index in the name column mysql > show variables like'% iso%' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | REPEATABLE-READ | +-+-+ 1 row in set (0.01sec)

Mysql > select * from t_test4 order by name; +-+-+ | id | name | +-+-+ | 6 | hubei | 5 | wuhan | 2 | zhej | 4 | zhej | 5 | zhej | +-+-+ 7 rows in set (0.00 sec)

Mysql > create index idx_name on t_test4 (name); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql > show index from t_test4 + -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+ | t_test4 | 1 | idx_name | 1 | name | A | 7 | NULL | NULL | YES | BTREE | +-+- -+-+ 1 row in set (0.01 sec)

-check to see if the UPDATE statement execution plan has gone from the newly created index idx_name mysql > explain update id=7 where name='hubei'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'7 where name='hubei'' at line 1 mysql > explain update t_test4 set id=7 where name='hubei' +-+ | id | select_type | table | type | possible_keys | key | | key_len | ref | rows | Extra | +-- + | 1 | SIMPLE | T_test4 | range | idx_name | idx_name | 23 | const | 1 | Using where | +- -+ 1 row in set (0.01 sec)

-- enable transaction mysql > begin; Query OK, 0 rows affected (0.00 sec)

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

Session 159Execute INSERT INTO SQL wait timeout error mysql > insert into t_test4 values (8 HY000 Hubei'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Check lock information: 158session is blocked. 159session 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 | +- -+- - - -+ | 579773 | 159th | insert into t_test4 values (8) 'hubei') | 579770 | 158| 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 | View 158session transaction information: mysql > select * from information_schema.innodb_trx\ G * * 1. Row * * trx_id: 579770 trx_state: RUNNING trx_started: 2017-09-03 03:49:43 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 5 trx_mysql_thread_id: 158trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx _ lock_structs: 4 trx_lock_memory_bytes: 1184 trx_rows_locked: 3 Murray-locked 3 records 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 1 row in set (0.00 sec)

What is the reason? Because under the RR isolation level, MySQL introduces GAP locks to ensure repeatability. What is a GAP lock? Let's take a look at the definition: A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked. Gap is the lock between index records, before the first index record is satisfied and after the last index record is satisfied. The following figure (test example) focuses on looking carefully here, so I cannot insert hubei. In addition, let's see if GAP is as shown above. Data cannot be inserted before hubei, and data cannot be inserted between hubei and wuhan. After wuhan, it can be inserted normally: mysql > insert into t_test4 values (8 camera huhu');-- failed ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql > insert into t_test4 values (8 Magnum hubei') -- failed ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql > insert into t_test4 values;-- successful Query OK, 1 row affected (0.01 sec)

Let's see if this happens to the RC isolation level (remember to log out and log back in after changing the isolation level) session 1: mysql > set global tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec)

Mysql > use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A

Database changed mysql > start transaction; Query OK, 0 rows affected (0.00 sec)

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

Conversation 2 mysql > insert into t_test4 values; Query OK, 1 row affected (0.01 sec)

Mysql > insert into t_test4 values; Query OK, 1 row affected (0.00 sec)

Mysql > insert into t_test4 values; Query OK, 1 row affected (0.00 sec)

Mysql > select * from information_schema.innodb_trx\ G

* * 1. Row *

Trx_id: 579785

Trx_state: RUNNING

Trx_started: 2017-09-03 04:29:57

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 4

Trx_mysql_thread_id: 168

Trx_query: select * from information_schema.innodb_trx

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 0

Trx_lock_structs: 3

Trx_lock_memory_bytes: 360

Trx_rows_locked: 2

Trx_rows_modified: 1

Trx_concurrency_tickets: 0

Trx_isolation_level: READ COMMITTED

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.00 sec)

It can be seen that this situation does not exist in RC quarantine.

Summary:

Isolation level no suitable index RC locks only records that need to be updated RR locks all records because of GAP locks, locks between index records, multiple locks

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.