mysql 锁

2023-02-24 14:07:49

Lock table有两种模式

lock tables table_name read [or write];

test1:

session 1:

lock tables tmp_xf_lock;

1. 可以查询

2. dml 报：ERROR 1099 (HY000): Table 'tmp_xf_lock' was locked with a READ lock and can't be updated

session 2:

1. 可以查询

2. dml：等待，直到session 1 unlock tables 或者超时

test2:

session1:

lock tables tmp_xf_lock write;

1. 可以查询

2. 可以dml ：insert into tmp_xf_lock values(8,8);

Query OK, 1 row affected (0.00 sec)

session2:

1. 不可以查询

2，不可以dml ，都是等待状态

这些文档里描写的很清楚，所以当只是想停止对表加锁，不让表表数据再发生变更，那么用 read。如果只是想让自己可以更改数据，其他用户不能查询也不能变更数据，那么用 wirte（阻塞了其他线程的读写，有点狠，都不让读了）。用处各不相同，注意好选择。看字面意思的 lock write可能会产生误解。

注意：FLUSH TABLES WITH READ LOCK; 这样可以锁住所有表

Lock tables，主要应用于非innodb类型的表的事务操作

如果你的表都是innodb，就不需要lock table了。当事务与lock tables一起使有时，需要注意以下内容：

（1）Start transaction或者begin运行后，如果之前运行了lock tables ….，会自动解锁，同时也会自动提交前面有开始的事务

（2）commit和rollback只对事务有影响，不会对lock tables产生解锁作用。

（3）lock tables运行后，如果前面运行了start transaction，这里的lock tables就带有commit作用，自动事务提交---------lock table 与事务不能共存

（4）lock tables与事务混用时必须这样：(其实这里相当于模拟了一个事务(innodb 没有必要这样做))

set autocommit = 0;

lock tables 表1 write,表2 read ….;

。。。一些update,delete,insert等；

commit;

unlock tables;

Lock tables 是表锁事务开始的时候一般是行锁有时间也是表锁

A端——使用mySQL命令行

B端——使用phpMyAdmin

数据表：create table t_role_user(id int not null auto_crement,rid int,uid int,primary key(id));

在A端运行：

Start transaction;

update t_role_user set rid=99 where id=1;

接着在B端

Update t_role_user set rid=100 where id=2;

B端正常完成，说明此时表没有被锁

接着在B端

Delete from t_role_user where id=1;

B端的phpMyAdmin页面运行中，进程在等待A端事务完成，因为id=1这行被锁定了

接着在A端commit

然后再看B端发现进程完成了，成功删除id=1表了，这是因为A端的事务完成了，id=1的行解锁了，B端才可以继续

OK，我们删除t_role_user表的id字段，只留rid与uid两个字段，再测试：

A端

Start transaction;

update t_role_user set rid=12 where rid=1;

接着在B端

Update t_role_user set rid=100 where rid=2;

B端的phpMyAdmin页面运行中，此时因为update没指定主键，虽然B端更新的行不是A端事务操作的行，但t_role_user整个表还是被锁住了，所以B端必须等A端事务完成了才可以继续。

接着在A端Commit

再回头看B端，发现sql执行完毕了。

事务中update,delete指定主键可以帮助innodb锁定行而不需要锁定整个表，我再测试对rid与uid建立联合的唯一索引，如果在事务中update或delete有指定rid=? And uid=?时，innodb也只会锁行不会锁表，说明唯一索引也可以帮助innodb锁行不锁表

事务之间的锁定分的情况：

1:一个用户开始事务另一个没有

2：一个用户开始事务另一个也开始事务

不论是一还是二的情况都满足上边的

下边介绍一下第2中的几个不同情形：

事务的分离水平（就是锁）有以下三种；

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

当用户设置这个级别的话没有任何限制就相当于没有锁一样

Select 的时候没有限制只要那个开启事务一端 dml 语句这边查看随时变化

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

当用户设置这个级别的话类似于一个开启事务一个没有的情况

Select 的时候只能查到没有commit 之前的

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

这个必须两端都设置成这个级别才能开到效果，只要开启事务用的了dml语句

另一端select 处于等待状态直到 commit

实验截图：

一端开启事务一端没有

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

这个与一端开启一端没有开启效果一样

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

先查询一下看看

这三种级别说着有点模糊自己做个实验看看就明白了

可以用下边的代码做实验

CREATE TABLE `customer`(

`mid` CHAR(5) PRIMARY KEY NOT NULL,

`nam` VARCHAR(20) NOT NULL DEFAULT '',

`birth` DATE NOT NULL DEFAULT '00-00-00',

`sex` CHAR(1) NOT NULL DEFAULT '0'

) engine=innodb

INSERT INTO customer VALUES('G0001','杜意意','1975-04-18','0');

INSERT INTO customer VALUES('G0002','李玉枝','1980-09-09','1');

INSERT INTO customer VALUES('H0001','李如','1975-04-18','0');

INSERT INTO customer VALUES('N0001','小小','1980-11-23','1');

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

begin;

SELECT mid,nam FROM customer WHERE mid='G0001';

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

begin;

UPDATE customer set nam='周同' WHERE mid='G0001';

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

begin;

INSERT INTO customer VALUES('T0001','王二','1980-11-23','1');

码农公寓

相关文章