ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

来源：互联网发布：域名备案需要哪些资料编辑：程序博客网时间：2024/06/05 20:17

session1：

mysql> show create table t \G;
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) DEFAULT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> lock table t read;
Query OK, 0 rows affected (1 min 3.82 sec)

mysql> show open tables from test;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | t | 1 | 0 |
| test | t1 | 0 | 0 |
+----------+-------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select * From t;
+------+------+
| id | name |
+------+------+
| 1 | aaaa |
| 2 | bbb |
| 3 | ddd |
| 4 | ttt |
+------+------+
4 rows in set (0.00 sec)

mysql> select * From t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
mysql> update t set name='a';
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> insert into t1 values(1,'aa');
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
mysql> delete from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

mysql> insert into t values(5,'e');
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> update t set name='c';
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> delete from t;
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated

在打开一个sesion2：

mysql> select * From t;
+------+------+
| id | name |
+------+------+
| 1 | aaaa |
| 2 | bbb |
| 3 | ddd |
| 4 | ttt |
+------+------+
4 rows in set (0.00 sec)

mysql> select * From t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | d |
| 3 | d |
+------+------+
4 rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 2 rows affected (0.03 sec)

mysql> select * From t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+

mysql> insert into t1 values(3,'d');
Query OK, 1 row affected (0.03 sec)
2 rows in set (0.00 sec)

mysql> insert into t values(3,'d');
卡住

mysql> delete from t;

卡住

update t set name='a';

卡住

结论：通常是session1因为某个进程锁了T，在没有释放锁时，想要访问另外一个T1，就会报ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

在mysql中，如果某个session使用lock table tname read|write锁定某个表，

那么同一个session中只能对锁定的表进行查询操作，不能对锁定的表进行更新插入删除，不允许对没有锁定的表进行查询更新插入删除，

其他session对锁定的表只能查询，不能进行update，insert，delete操作

其他session对没有锁定的表仍然可以进行select、update、insert、delete操作

0 0