Row Lock of mysql innodb (6)-- unsafe statement Lock
In general, select uses mvcc technology, which does not add row locks, but for insert. Select, create table.. Unsafe statements such as select will automatically add a shared lock to the source table
Of course, whether the lock is also controlled by the following parameters, because the lock is not due to the isolation level, but for replication security.
Root@sakila 11:03:59 > show variables like'% unsafe%'
+-+ +
| | Variable_name | Value |
+-+ +
| | innodb_locks_unsafe_for_binlog | OFF |
+-+ +
1 row in set (0.00 sec)
Session 1:
Root@sakila 11:02:28 > insert into target select * from tab_no_index
Query OK, 7 rows affected (0.01sec)
Records: 7 Duplicates: 0 Warnings: 0
Session 2:
Root@sakila 11:03:02 > update tab_no_index set name=name where name='1'
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
*
If you modify the variable, the source table of the above select will not be locked, but there will be problems with replication in statement replication mode
Set global innodb_locks_unsafe_for_binlog=1
Mysqlbinlog mysql-bin.000039 | the result of more is analyzed as follows: because the record binlog is recorded in the order of submission, insert is executed. Before the select statement, update has been executed, and the main library executes insert.. first. Select, then execute update. Therefore, the results of the two are not the same, so they are not safe.
# 170312 23:18:14 server id 2552763370 end_log_pos 328 CRC32 0x6262bb7c Query thread_id=2 exec_time=0 error_code=0
Use `sakila` / *! * /
SET timestamp 1489331894
Update tab_no_index set name='8' where name='1'
/ *! * /
# at 328
# 170312 23:18:32 server id 2552763370 end_log_pos 359 CRC32 0xebeef64e Xid = 20
Com _ MIT _ blank /
# at 359
# 170312 23:17:41 server id 2552763370 end_log_pos 442 CRC32 0xae75ad5e Query thread_id=3 exec_time=0 error_code=0
SET timestamp 1489331861
BEGIN
/ *! * /
# at 442
# 170312 23:17:41 server id 2552763370 end_log_pos 580 CRC32 0x3368f120 Query thread_id=3 exec_time=0 error_code=0
SET timestamp 1489331861
Insert into target select * from tab_no_index where name='1'
/ *! * /
# at 580
# 170312 23:18:48 server id 2552763370 end_log_pos 611 CRC32 0x1b39b7d7 Xid = 19
Com _ MIT _ blank /
DELIMITER
# End of log file
ROLLBACK / * added by mysqlbinlog * /
/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/
Summary:
The best way to avoid locking without affecting replication is:
Innodb_locks_unsafe_for_binlog = 1 (allow unsafe statements)
In the meantime
Binlog_format=row (avoid unsafe statements)
