mysql之U锁

MYSQL：
窗口1：插入1065不提交
mysql> create table employee1(id int not null  primary key,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into employee1 values(1065,'liys');
Query OK, 1 row affected (0.00 sec)

mysql> 

窗口2：插入1066会成功，但是插入1065会锁等

mysql> insert into employee1 values(1066,'liyrr'); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee1 values(1065,'liyrr');

窗口3：锁等信息
mysql> select * from information_schema.innodb_locks;
+---------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table          | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
| 203965:29:3:2 | 203965      | S         | RECORD    | `mysql`.`employee1` | PRIMARY    |         29 |         3 |        2 | 1065      |
| 203963:29:3:2 | 203963      | X         | RECORD    | `mysql`.`employee1` | PRIMARY    |         29 |         3 |        2 | 1065      |
+---------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 203965            | 203965:29:3:2     | 203963          | 203963:29:3:2    |
+-------------------+-------------------+-----------------+------------------+

可以看到MySQL并没有申请U锁。继续测试，可以看到name没有索引，在select查询时候使用的是全表扫描，会直接申请每一行的X锁。
窗口1：
mysql> select * from employee1;
+----+--------+
| id | name   |
+----+--------+
|  1 | liys4  |
|  2 | liys66 |
+----+--------+
2 rows in set (0.00 sec)

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

mysql> update employee1 set id=11 where name='liys4';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

窗口2：
mysql> update employee1 set id=22 where name='liys66';
窗口3：
mysql> select * from information_schema.innodb_locks;
+---------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table          | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
| 203985:29:3:2 | 203985      | X         | RECORD    | `mysql`.`employee1` | PRIMARY    |         29 |         3 |        2 | 1         |
| 203980:29:3:2 | 203980      | X         | RECORD    | `mysql`.`employee1` | PRIMARY    |         29 |         3 |        2 | 1         |
+---------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 203985            | 203985:29:3:2     | 203980          | 203980:29:3:2    |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
 

如果走索引的话就不会出现这个情况
窗口1；
mysql> select * from employee1;
+----+--------+
| id | name   |
+----+--------+
|  1 | liys4  |
|  2 | liys66 |
+----+--------+
2 rows in set (0.00 sec)

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

mysql> update employee1 set name='liys01' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
窗口2：
mysql> update employee1 set name='liys02' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
窗口3：
mysql> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec)

进一步测试：看起来即便设置了最高级别的隔离级别SERIALIZABLE; select仍然没有加S锁
窗口1：
mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from employee1 where name='liys01';
+----+--------+
| id | name   |
+----+--------+
| 10 | liys01 |
+----+--------+
1 row in set (0.00 sec)

mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | SERIALIZABLE   |
+-----------------------+----------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select * from employee1 where name='liys01';
+----+--------+
| id | name   |
+----+--------+
| 10 | liys01 |
+----+--------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee1 where name='liys01';
+----+--------+
| id | name   |
+----+--------+
| 30 | liys01 |
+----+--------+
1 row in set (0.01 sec)
  
窗口2：
mysql> update employee1 set id=30 where name='liys01';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

窗口3：
mysql> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec)