mysql 锁24小时_mysql锁

1、锁简介

数据库中的锁是指一种软件机制，用来控制防止某个用户(进程会话)在已经占用了某种数据资源时，其他用户做出影响本用户数据操作或导致数据非完整性和非一致性问题发生的手段。

2、锁的级别

按照锁级别划分，锁可分为共享锁、排他锁。

A、共享锁(读锁)

针对同一块数据，多个读操作可以同时进行而不会互相影响。

共享锁只针对UPDATE时候加锁，在未对UPDATE操作提交之前，其他事务只能够获取最新的记录但不能够UPDATE操作。

B、排他锁(写锁)

当前写操作没有完成前，阻断其他写锁和读锁。

3、锁的粒度

按锁的粒度划分，锁可分为表级锁、行级锁、页级锁。

A、行级锁

开销大，加锁慢，会出现死锁，锁定力度最小，发生锁冲突的概率最低，并发度高。

B、表级锁

开销小，加锁快，不会出现死锁，锁定力度大，发生冲突所的概率高，并发度低。

C、页面锁

开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定力度介于表和行行级锁之间，并发度一般。

4、MySQL存储引擎和锁机制

MySQL的锁机制比较简单，最显著的特点是不同的存储引擎支持不同的锁机制。

MyISAM和MEMORY存储引擎采用表级锁。

InnoDB支持行级锁、表级锁，默认情况采用行级锁。

五、表级锁

1、表级锁简介

MyISAM存储引擎和InnoDB存储引擎都支持表级锁。

MyISAM存储引擎支持表级锁，为了保证数据的一致性，更改数据时，防止其他人更改数据，可以人工添加表级锁。可以使用命令对数据库的表枷锁，使用命令对数据库的表解锁。

给表加锁的命令Lock Tables，给表解锁的命令Unlock Tables

MyISAM引擎在用户读数据自动加READ锁，更改数据自动加WRITE锁。使用lock Tables和Unlock Tables显式加锁和解锁。

2、添加表级读锁

打开会话1，创建表

CREATE TABLE tc

(

id INT,

name VARCHAR(10),

age INT

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入两条记录：

insert into tc values(1, '孙悟空', 500);

insert into tc values(3, '猪八戒', 100);

对表加READ锁

lock tables tc read;

加锁后只可以查询已经加锁的表，

select * from tc;

查询没有加锁的表将失败

select * from ta;

打开会话2，对已经加锁的表进行查询，成功。

select * from tc;

对加锁的表tc进行更新操作，将失败

update tc set age=100 where id=1;

会话1中使用LOCK TABLE命令给表加了读锁，会话1可以查询锁定表中的记录，但更新或访问其他表都会提示错误；会话2可以查询表中的记录，但更新就会出现锁等待。

在会话1对表进行解锁，会话2的更新操作成功。

unlock tables;

在会话1，再次锁定表tc，后面带local参数。

lock tables tc read local;

Local参数允许在表尾并发插入，只锁定表中当前记录，其他会话可以插入新的记录

在会话2插入一条记录

insert into tc values(2, '唐僧', 20);

在会话1查看tc表的记录，无插入记录

select * from tc;

3、设置表级锁并发性

READ锁是共享锁，不影响其他会话的读取，但不能更新已经加READ锁的数据。MyISAM表的读写是串行的，但是总体而言的，在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，用以控制其并发插入的行为，其值分别可以为0、1或2。

0：不允许并发操作

1：如果MyISAM表中没有空洞(即表的中间没有被删除的行)，MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录，是MySQL的默认设置。

2：无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

在MySQL配置文件添加，concurrent_insert=2，重启mySQL服务设置生效。

4、验证表级锁的并发性

设置concurrent_insert为0

在会话1对表tc加锁

lock tables tc read local;

在会话2插入一条记录，此时tc表被锁定，进入等待

insert into tc values(4, '沙悟净', 30);

在会话1解锁表tc，此时会话2插入成功

unlock tables;

设置concurrent_insert为1

在会话1删除ID为3的记录

delete from tc where id=3;

在会话1对表tc加锁

lock tables tc read local;

在会话2插入一条记录，此时tc表被锁定，并且表中有空洞，进入等待

insert into tc values(5, '白骨精', 1000);

在会话1解锁表tc，此时会话2插入成功，此时表中已经没有空洞

unlock tables;

在会话1对表tc加锁

lock tables tc read local;

在会话2插入一条记录，插入成功，支持有条件并发插入

insert into tc values(6, '白骨精', 1000);

在会话1解锁表tc

unlock tables;

设置concurrent_insert为2

在会话1删除ID为5的记录，创造一个空洞

delete from tc where id=5;

在会话1对表tc加锁

lock tables tc read local;

在会话2插入一条记录，插入成功，支持无条件并发插入

insert into tc values(7, '蜘蛛精', 1000);

在会话1解锁表tc

unlock tables;

5、添加表级写锁

添加表级写锁语法如下：

LOCK TABLES tablename WRITE;

不允许其他会话查询、修改、插入记录。

六、行级锁

1、行级锁简介

InnoDB存储引擎实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)。MVCC的优点是读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发修改。事务加锁，是针对所操作的行，对其他行不进行加锁处理。

快照读：简单的SELECT操作，属于快照读，不加锁。

select * from table where ?;

当前读：特殊的读操作，INSERT/UPDATE/DELETE，属于当前读，需要加锁。

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

以上SQL语句属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

2、验证快照读

打开会话1，创建一个表，含ID、姓名、年龄

CREATE TABLE td

(

id INT ,

name VARCHAR(10),

age INT

)ENGINE=innoDB DEFAULT CHARSET=utf8;

在插入两条记录

insert into td values(1, '孙悟空', 500);

insert into td values(2, '猪八戒', 100);

在会话1开始事务

start transaction;

在会话1查询ID位1的记录信息

select * from td where id =1;

打开会话2，更新ID为1的age为1000

update td set age=1000 where id=1;

在会话2查看ID为1的age已经更新为1000。

select * from td where id =1;

在会话1查看ID为1的age，仍然为500。

select * from td where id =1;

在会话1提交事务

COMMIT;

在会话1查看ID为1的age，已经为1000。

3、验证当前读

在会话1开始事务

start transaction;

在会话1给select语句添加共享锁。

select * from td where id=1 lock in share mode;

在会话2，更新ID为1的age的值为100，进入锁等待

update td set age=100 where id=1;

在会话1提交事务

COMMIT;

会话2的更新操作成功。

4、验证事务给记录加锁

在会话1开始事务

start transaction;

在会话1更新ID为1的age的值为500。

update td set age=500 where id=1;

在会话2开始事务

start transaction;

在会话2更新ID为2的age的值为1000，此时进入锁等待

update td set age=1000 where id=2;

td表没有指定主键，事务不支持行级锁。会话1的事务给整张表加了锁。

在会话1提交事务，此时会话2的修改成功

COMMIT;

在会话2提交事务，解除对表的锁定

COMMIT;

在会话1，给表的ID增加主键

alter table td add primary key(id);

在会话1开始事务

start transaction;

在会话1更新ID为1的age的值为5000

update td set age=5000 where id=1;

在会话2上开始事务

start transaction;

在会话2上修改ID为2的get的值为10000，更新成功，说明会话1只锁定了ID为1的行。

update td set age=10000 where id=2;

在会话2上更新ID是1的age值为100，出现等待。因为会话1给ID为1的行添加了独占锁。

update td set age=5000 where id=1;

在会话1提交事务

COMMIT;

在会话2提交事务

COMMIT;

在会话1查询，会话1和会话2对age列的修改都生效

select * from td;

5、死锁的产生

A事务添加共享锁后，B事务也可以添加共享锁。A事务UPDATE锁定记录，处于等待中，于此同时B事务也UPDATE更新锁定的记录，就产生死锁。

在会话1开始事务

start transaction;

在会话1查询ID是1的记录，并添加共享锁。

select * from td where id=1 lock in share mode;

在会话2开始事务

start transaction;

在会话2查询ID是1的记录，并添加共享锁。

select * from td where id=1 lock in share mode;

在会话1更新ID为1的age值为，等待会话2释放共享锁

update td set age=200 where id=1;

在会话2更新ID为1的age为，会话2发现死锁，回滚事务。

update td set age=200 where id=1;

在会话1提交事务

COMMIT;

七、事务实例

事务提交还是回滚，可以在事务结束处判断是否出现错误，如果出现，回滚。如果没有错误，提交事务。

使用自定义条件来决定事务是提交还是回滚。

1、由错误决定事务提交或回滚

在存储过程中使用事务，在事务的末尾判断是否有错误，插入失败，则回滚事务。

创建两张表，存储ID、姓名、年龄，创建存储过程将A表的指定ID的记录转移到B表。

CREATE TABLE ta

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(10),

age INT

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into ta values(1, '孙悟空', 500);

insert into ta values(2, '唐僧', 30);

CREATE TABLE tb

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(10),

age INT

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tb values(1, '孙悟空', 500);

insert into tb values(3, '猪八戒', 100);

CREATE PROCEDURE move(num INT)

BEGIN

DECLARE errorinfo INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errorinfo=1;

START TRANSACTION;

INSERT INTO tb SELECT * FROM ta WHERE id=num;

DELETE FROM ta WHERE id=num;

IF errorinfo=1

THEN ROLLBACK;

ELSE

COMMIT;

END IF;

END

将ID为2的记录从A表转移到B表

call move(2);

2、由自定义条件决定事务提交或回滚

创建两个表，每个表含账户、姓名、余额信息，创建一个存储过程，从A表中的一个账户转账一定金额到B表的一个账户，如果转出账户的余额不足，则回滚，否则提交。

create table accountA

(account INT PRIMARY KEY NOT NULL,

name VARCHAR(10),

balance DOUBLE

)ENGINE=innoDB default CHARSET=utf8;

insert into accountA VALUES(1, '孙悟空', 10000);

insert into accountA VALUES(2, '唐僧', 20000);

create table accountB

(account INT PRIMARY KEY NOT NULL,

name VARCHAR(10),

balance DOUBLE

)ENGINE=innoDB default CHARSET=utf8;

insert into accountB VALUES(1, '孙悟空', 10000);

insert into accountB VALUES(2, '唐僧', 20000);

CREATE PROCEDURE transfer(fromaccout INT,toaccount INT, num DOUBLE)

BEGIN

DECLARE m DOUBLE;

START TRANSACTION;

UPDATE accountB SET balance=balance + num WHERE account=toaccount;

UPDATE accountA SET balance=balance - num WHERE account=fromaccout;

SELECT balance INTO m from accountA WHERE account=fromaccout;

IF m < 0

THEN ROLLBACK;

ELSE

COMMIT;

END IF;

END

从A表的账户2转出25000元到B表的账户2。

call transfer(2,2,25000);

此时A表的余额不足，回滚