表名:lock_read
表类型:MyISAM(此引擎只支持表级锁,不支持行级锁,下面是测试表级锁)
锁类型:表锁
表结构:CREATE TABLE `lock_read` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
`name` char(30) NOT NULL DEFAULT '' COMMENT '姓名',
`age` tinyint(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=`MyISAM` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=FIXED COMMENT='测试表级锁' CHECKSUM=0 DELAY_KEY_WRITE=0;
添加一条数据:INSERT INTO `lock_read` (`name`, `age`) VALUES ('小龚', 22);
终端一1、查询本身数据
SELECT * FROM lock_read;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 小龚 | 22 |
+----+--------+-----+
2、加表锁
LOCK TABLES lock_read READ;
3、更新数据
UPDATE lock_read SET age=23 WHERE id=1;
报错:Table 'lock_read' was locked with a READ lock and can't be updated
解释:意思是表当前加锁中,不能被任何人更新(包括当前进程)
终端二1、查询本身数据
SELECT * FROM lock_read;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 小龚 | 22 |
+----+--------+-----+
2、加表锁
LOCK TABLES lock_read READ;
3、更新数据
UPDATE lock_read SET age=24 WHERE id=1;
报错:Table 'lock_read' was locked with a READ lock and can't be updated
解释:意思是表当前加锁中,不能被任何人更新(包括当前进程)
但是如果当前进程跳过步骤2,不对表进行加锁,那么是这样子的:
前面进程已加锁了,那么当前进程就会进入阻塞状态,等到终端一的进程释放锁后,这条sql才能被执行成功
终端一1、解锁
UNLOCK TABLES;
2、更新数据
UPDATE lock_read SET age=25 WHERE id=1;
A、如果终端二加锁过了,那么当前进程就会进入阻塞状态,等待终端二的进程解锁后,这条sql才能被只成功
B、如果终端二没有加锁,那么当前sql将执行成功