mysql 锁

时间:2022-03-28 22:14:58
mysql 锁

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 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');