mysql 禁止更新列_Mysql RR隔离更新列没有索引 会锁全表

mysql> show variables like '%tx_isolation%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

Session 1:

mysql> show index from test;

Empty set (0.00 sec)

mysql> update test set name='xxxx' where id=2;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

Session 2:

mysql> select * from test;

+------+------+

| id | name |

+------+------+

| 1 | b |

| 11 | aa |

| 2 | xxxx |

| 10 | a |

+------+------+

4 rows in set (0.00 sec)

mysql> update test set name='xxxx' where id=10;

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

没有索引的情况 ,RR隔离级别 是锁全表

//*********************************************************************

Session 1:

mysql> update test set id=99 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Session 2:

mysql> update test set id=100 where id=2; --HANG

//测试有主键的情况下:

mysql> CREATE TABLE `s100` (

-> `sn` int(11) NOT NULL AUTO_INCREMENT,

-> `id` int,

-> `info` varchar(40) DEFAULT NULL,

-> PRIMARY KEY (`sn`)

-> ) ENGINE=InnoDB AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 ;

Query OK, 0 rows affected (0.04 sec)

mysql> show index from s100;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| s100 | 0 | PRIMARY | 1 | sn | A | 0 | NULL | NULL | | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

Session 1：

mysql> update s100 set id=100 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Session 2:

mysql> update s100 set id=200 where id=2;---Hang

//测试更新是否锁全表:

mysql> explain update Product set

-> status = '3'

-> where status = '2' and buyToTime < '2016-05-10 12:54:00';

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | Product | index | NULL | PRIMARY | 4 | NULL | 506 | Using where |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.00 sec)

从possible_keys中所选择使用的索引

正常走索引的更新是:

mysql> explain update test set id=100 where id=1;

+----+-------------+-------+-------+---------------+-----------+---------+-------+------+------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+-----------+---------+-------+------+------------------------------+

| 1 | SIMPLE | test | range | test_idx1 | test_idx1 | 5 | const | 1 | Using where; Using temporary |

+----+-------------+-------+-------+---------------+-----------+---------+-------+------+------------------------------+

1 row in set (0.00 sec)