mysql哪些操作会产生表锁 mysql什么时候用表锁

转载

mob64ca14133dc6 2023-12-31 21:28:38

文章标签 mysql哪些操作会产生表锁死锁 MySQL mysql 文章分类 MySQL 数据库

关于innodb的锁（一）

什么时候使用表锁

对于innodb表，在绝大部分情况下都应该使用行锁，因为我们选择innodb的原因往往是因为它支持事务和行锁。但是也存在特殊的情况需要考虑使用表锁。

1、事务需要更新大部分甚至全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突
2、事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。

在实际中，上述两种情况不应太多出现，否则，MyISAM表会是我们更好的选择

需要注意的是：

1、我们可以通过lock tables命令给innodb加表锁，但是，表锁不是由innodb存储引擎层管理的，而是由上一层的MySQL Server层负责的。仅当autocommit=0、innodb_table_locks=1时，innodb层才能知道MySQL加的表锁，MySQL Server也才能感知innodb加的行锁，这种情况innodb才能自动识别涉及表锁的死锁。

2、在使用locck tables命令对innodb加表锁的时候，要注意把autocommit设置为0，否则MySQL不会给表加锁；事务结束前，不要用unlock tables释放表锁，因为unloc tables命令会隐含的提交事务；

3、commit和rollback并不会释放lock tables加的表锁，必须要使用unlock tables释放表锁。

死锁

对于inndob引擎来说，除了单个SQL组成的事务外，锁是逐步获得的，这就决定了innodb中会发生死锁。
例如：
mysql> select * from test;
±-----±-----+
| id | name |
±-----±-----+
| 1 | a |
| 1 | b |
±-----±-----+
2 rows in set (0.00 sec)

事务1：
mysql> select * from t1 where id=1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

事务2：
mysql> select * from t2 where id=1 for update;
 +------+------+
| id   | name |
+------+------+
|    1 | b    |
+------+------+
1 row in set (0.00 sec)

事务1：
mysql> select * from t2 where id=1 and name='b' for update;
等待

事务2：
mysql> select * from t1 where id=1 for update;
发生死锁

发生死锁后，innodb一般都能自动检测到，并使其中一个事务rollback，另一个事务获得锁，继续完成事务。但是在涉及到外部锁或者表锁的时候，innodb并不能完全自动检测到死锁，这时就需要设置锁等待超时参数innodb_lock_wait_timeout。此外，这个参数并不是只解决死锁问题，在高并发情况下，如果大量事务因为无法立即获得锁而挂起的话，会占用大量的资源，造成严重的性能问题，我们通过设置这个参数可以有效的避免这种情况。