共享锁select * from t1 where id=2 LOCK IN SHARE MODE;

1.SESSION A:

mysql> select * from t1;
+——+
| id |
+——+
| 99 |
| 2 |
+——+
2 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.12 sec)

mysql> > select * from t1 where id=2 LOCK IN SHARE MODE;
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 ‘> select * from t1 where id=2 LOCK IN

SHARE MODE’ at line 1
mysql> select * from t1 where id=2 LOCK IN SHARE MODE;
+——+
| id |
+——+
| 2 |
+——+
1 row in set (0.00 sec)

1. SESSION B:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set id=20 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

关闭自动提交的情况下,共享锁会堵塞其他会话的更新

在自动提交下,不会堵塞