一、mysql数据库锁分为表锁和行锁,主要是用来处理并发,当多个线程对同一个对象进行操作,如果不加控制,会发生数据错误。
二、表锁
1.表级锁,锁住整张表,InnoDB和MyISAM都支持表级锁,但随着并发的增多,执行的速度也会越来越慢。
2.表级锁,分为,读锁、写锁。
lock table user_balance read; #读锁 / lock table user_balance write; #写锁
select * from user_balance;
unlock tables;
3.读锁 :
如果一张表被锁住,无论是本地会话,还是其他会话,只能执行select,不能执行更新操作(update insert delete)
sql:
LOCK TABLE 表名 READ;
(select操作)
UNLOCK TABLES;
4.写锁(只允许本事务读、更新操作,其他事务都不可以)
只能本地会话执行select ,update,insert ,delete ,其他会话不能执行任何操作。
5.在存储过程中,是不能使用表级锁lock的。
三、行锁
1.InnoDB支持,是mysql的最小粒度锁,也是真正的事务锁。
行锁是索引级别的,必须在会话中,以索引为条件,否则会锁住整张表,.
2.共享锁,在事务中执行,对加了锁的sql,只有本地会话可以执行修改,直至提交了commit,其他会话才能对这一行修改,过程中,其他会话可以读(select)
start transaction;
select *** lock in share mode;
commit;
3.排他锁,在事务中执行,具备共享锁的特点,不能在其他会话中,对已经加了锁的行,再加锁。
start transaction;
select *** for update;
commit;
三、使用存储过程,模拟用户充值,并使用表级锁实现并发控制。
1.步骤 (充值日志表,余额表,参照上一篇文章)
- 向充值日志表插入数据,插入成功
- 在余额表中,判断用户是否存在,如果存在,执行update,余额累加/减,如果不存在,则执行insert
- 如果其中某一个出现异常,则整个事务回滚。
BEGIN
#Routine body goes here...
DECLARE err int DEFAULT 0;
DECLARE num int DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set err=1;
start TRANSACTION;
insert into user_balance_log(user_id,log_type,log_value,log_des) values(_user_id,_log_type,_log_value,_log_des);#插入日志表
if ROW_COUNT()>0 THEN
#查看在用户余额表中,有没有这个用户
select count(*) into num from user_balance where user_id=_user_id;
if num>0 THEN
update user_balance set user_money=user_money+_log_value where user_id=_user_id;#有这个用户则更新用户余额
else
insert into user_balance(user_id,user_money) values(_user_id,_log_value); #没有这个用户则添加新的用户余额记录
end if;
end if;
if _is_stop=1 THEN
select SLEEP(7);#通过_is_stop参数为1时来模拟服务器卡顿的现象
end if;
if err=1 THEN
ROLLBACK;
else
COMMIT;
end if;
END
参数:In _user_id int,In _log_type tinyint,In _log_value decimal(10,2),In _log_des varchar(200)
2.使用表级锁中的写锁来处理并发
lock table user_balance write;
call sp_set_money(1,1,50,'用户充值',1);
unlock tables;
四、使用行级锁、存储过程模拟用户转账功能。
1.需求,服务器存在卡顿的情况,A用户向B用户转账。
2.创建存储过程sp_transfer_money
BEGIN
#Routine body goes here...
DECLARE A_money decimal(10,2) default 0;
DECLARE B_money decimal(10,2) default 0;
DECLARE err tinyint default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set err=1;
start TRANSACTION;
select sleep(7);#模拟服务器卡顿
#给A用户和B用户所在的行添加行级排它锁, 其他会话不能加锁也不能修改,只能查询
#因为行级锁是索引级的,所以要先给user_id字段添加索引,我添加的是普通索引
select user_money into A_money from user_balance where user_id=_A_id for UPDATE;
select user_money into B_money from user_balance where user_id=_B_id for UPDATE;
if A_money<_money THEN
COMMIT;
select "余额不足,无法转账" as result;
ELSE
set A_money=A_money-_money;
set B_money=B_money+_money;
update user_balance set user_money=A_money where user_id=_A_id;
update user_balance set user_money=B_money where user_id=_B_id;
end if;
if err=1 THEN
ROLLBACK;
select "sql出错" as result;
ELSE
COMMIT;
select "转账成功" as result;
end if;
END
参数:In _A_id int,In _B_id int,In _money decimal(10,2)
调用存储过程:call sp_transfer_money(1,2,50);