mysql select 混合存储过程

转载

数据探索先锋 2025-01-07 08:59:55

文章标签 mysql select 混合存储过程 mysql 存储过程并发处理订购系统 文章分类 MySQL 数据库

本实验用mysql模拟一个多用户在线的订房业务，其实只要是有人数限制的订购活动，其主要业务逻辑都是相似的，所以这个实验对于订房，订票等等竟争性资源的访问都有一定的启发作用。

基础数据准备：

新建一个room表，每个房间中可住人数，已住人数，模拟订房功能。

CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomNo` varchar(10) DEFAULT NULL,
  `capacity` int(255) DEFAULT NULL,
  `users` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一百个房间信息

[SQL]insert into room(roomNo,capacity,users)
select LPAD(i,3,0),i%4+1,0 from nums where i<=100

再增加一张表，记录用户

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userNo` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

再增加一张表roomUser,记录用户所住的房间

CREATE TABLE `roomuser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userNo` varchar(10) DEFAULT NULL,
  `roomNo` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

实验描述：两个会话，分别表示用户1与用户2,都想订房间004,现在房间剩余床位数为1，两者之间形成争用关系，

用一般的查询实现业务逻辑

通过实操可知，MYSQL不支持匿名块，只能在SP、UDF中使用if else, while 而不能在一般的查询界面中使用。比如直接在navicate查询窗口中执行如下的代码：

select capacity,users into @capacity,@users from room where roomNo = '004';
if @capacity > @users THEN
 select 'ok';
ELSE
 select 'no bed';
end if;

会报语法错误。为了避免这种语法错误，改写查询如下：

select capacity,users into @capacity,@users from room where roomNo = '004';
select case when @capacity > @users then 'ok' else 'no bed' end;

正式开始实验

模拟订房的会话如下：

#用户001
select '001' into @userNo;
#房间004
select '004' into @roomNo;
#检查是否还有空床位
select capacity,users into @capacity,@users from room where roomNo = @roomNo for update;
select case when @capacity > @users then 'ok' else 'no bed' end;
#更新房间的已住人数，插入住房记录
update room set users=users+1 where roomNo = @roomNo and @capacity > @users;
insert into roomUser(roomNo,userNo) select @roomNo,@userNo from dual where @capacity > @users;
#查看房间信息
select * from room where roomNo = '004';
#查看住房记录表
select * from roomUser;
 
#用户002
select '002' into @userNo;
#房间004
select '004' into @roomNo;
#检查是否还有空床位
select capacity,users into @capacity,@users from room where roomNo = @roomNo for update;
select case when @capacity > @users then 'ok' else 'no bed' end;
#更新房间的已住人数，插入住房记录
update room set users=users+1 where roomNo = @roomNo and @capacity > @users;
insert into roomUser(roomNo,userNo) select @roomNo,@userNo from dual where @capacity > @users;
#查看房间信息
select * from room where roomNo = '004';
#查看住房记录表
select * from roomUser;

再写一个房间004的房客退房的会话：

#房间004房客退房
#去掉相关的住房记录
delete from roomuser where roomNo ='004';
#设置房间人数为0
update room set users = 0 where roomNo ='004';

将以上的查询分别保存为 “用户1订房”“用户2订房”，“退房”。打开这3个查询，依次执行：“退房”，“用户1订房”“用户2订房”，查看执行的效果。

由于查询语句执行的速度很快，房间004的容量只有1，如果用户1订房了，并且未退房，则用户2订房会失败，反之亦然。

为了模拟并发时的情况，假设用户1在查看了房间情况后，有一个思考的时间，才开始真正的订房操作。

因此修改用户1订房中的语句为：

select case when @capacity > @users then 'ok' else 'no bed' end,sleep(5) thinking;

模拟思考5秒的情形

而用户2没有思考的时间。

依次执行：“退房”，“用户1订房”“用户2订房”。结果不出所料

用户2在用户1思考的时候把房间订了，用户1思考完也把房间订了，房间004容量只有1，但是住进了两个人。

为了避免以上超订的情况出现，用户001使用select for update ，试图阻止用户2在自已订房完成前进行看房操作。

在“用户1订房”查询中进行修改：

select capacity,users into @capacity,@users from room where roomNo = @roomNo for update;

增加了 for update 子句。

依次执行：“退房”，“用户1订房”“用户2订房”。用户2并没有因为用户1的”for update”而被阻止了“看房”，还是出现了超订。

那么在“用户2订房” 查询中进行修改，增加了 for update 子句。效果又如何呢？

实验结果并没有发生阻塞的效果，还是出现了超订。

怀疑“用户1订房”的多个语句没有形成一个事务，所以没有达到预期的效果，再尝试做以下改进：

检查一下隔离级别：SELECT @@tx_isolation;

REPEATABLE-READ

这是mysql 的默认隔离级别

修改，“用户1订房”，

…

#开启事务
start transaction;
#检查是否还有空床位
select capacity,users into @capacity,@users from room where roomNo = @roomNo for update;
select case when @capacity > @users then 'ok' else 'no bed' end,sleep(5);
#更新房间的已住人数，插入住房记录
update room set users=users+1 where roomNo = @roomNo and @capacity > @users;
insert into roomUser(roomNo,userNo) select @roomNo,@userNo from dual where @capacity > @users;
#提交事务
commit;
…

也就是用事务把查看房与订房操作等多个语句包裹起来。

“用户2订房”不作修改

依次执行：“退房”，“用户1订房”“用户2订房”

在用户1“思考”的时侯，用户2被阻塞了，直到用户1完成了订房操作，用户2再执行“看房”操作，这时房间已经没有了空床位。从而达到了防止超订的效果。

很显然，实际应用中，所有的用户都应把查看房间与订房操作等多个语句包裹成一个事务。

接下来，要验证一下，在“用户1订房”这个模拟的耗时较长的事务中，是只锁住了房间004这条记录，还是锁住了整个room表，这时我们将用户2的订房目标改为房间001，检查是否还会产生阻塞。

退房查询修改如下：

#房间004,001 合部房客退房
#去掉相关的住房记录
delete from roomuser where roomNo in('004','001');
#设置房间人数为0
update room set users = 0 where roomNo in('004','001');
将用户2的订房目标改为房间001
#用户002
select '002' into @userNo;
#房间001
select '001' into @roomNo;
…

依次执行：“退房”，“用户1订房”“用户2订房”

可能与很多人预期的不一致，用户2在看房时被阻塞了，不是说innodb支持行锁吗，为什么两个用户访问不同的行，还是会发生阻塞呢。

使用如下查询检查事务执行情况：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

会发现用户2对应的事务处于LOCK WAIT状态。

再做如下的实现试试，为room表的roomNo字段建立一个Normal-BTREE索引。

依次执行：“退房”，“用户1订房”“用户2订房”

这时会发现用户2没有被阻塞，原因何在？

原来innodb虽然支持行级锁，但是有条件的，也就是说只有在通过索引能找到数据时才会使用行级锁，如果使用了全表扫描才能确定数据，还是会使用表锁。

在没有建立roomNo的索引前，执行

explain select capacity,users into @capacity,@users from room where roomNo = ‘004’ for update;

会发现扫描类型是”all”

建立roomNo的索引后，执行

explain select capacity,users into @capacity,@users from room where roomNo = ‘004’ for update;

会发现扫描类型是”ref”

补充说明

（1），一定是能用索引取回数据才会使用行锁，不能想当然地认为建立了索引就会使用行锁，比如上一句如果改写成

explain select capacity,users into @capacity,@users from room where roomNo = 4 for update;

会发现扫描类型是”all”

因为 roomNo = 4 与roomNo = ‘004’不一样，两者类型不一致，不会使用索引。

（2）由于不能在一般的查询块中使用if else. 但是订房操作要使用 update与insert两个sql操作，要保证这两个语句有原子性，要么都成功，要么都不成功。因此这个业务最好是包装成存储过程，在存储过程中可以有条件地rollback或commit。如果是在jsp,php中有条件地rollback或commit,应考虑到网络延迟问题，如果网络不稳定，延迟时间长，还是在存储过程中最安全，不过存储过程代码不好维护也是一个问题。

下面是用存储过程所做的一个比较完整的例子

CREATE DEFINER=`root`@`localhost` PROCEDURE `bookRoom`(IN `roomNo_in` varchar(10),IN `userNo_in` varchar(10))
BEGIN
         #一个示例性的订房过程，由于人数有限制，所以采用了事务处理，避免超订现象的发生
         #捕获sql异常
         DECLARE txn_error INTEGER DEFAULT 0 ;
         DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET txn_error=1;        
         #由于不支持goto，所以用@err做标志，避免多层if..ELSE...嵌套
         set @err = 0;
         #检查是否已入住
         #为提高查询效率，要考虑在roomUser表上userNo字段建立索引
         if exists(select id from roomuser where userNo = userNo_in) THEN
                   set @err = 1;
                   set @msg = '已经入住';
         end IF;
         if @err = 0 then
                   #开启事务
                   start transaction;
                   #检查是否还有空床位，为了保证只锁定单行，请确保roomNo上已建索引，并且索引要能返回数据
                   select capacity,users into @capacity,@users from room where roomNo = roomNo_in for update;
                   if   @users >= @capacity THEN
                            set @err = 2;
                            set @msg = '没有空床位';
                            COMMIT;
                   end if;
         end if;        
         if @err = 0 then                                   
                   set @msg = '找到床位';
                   #更新房间的已住人数
                   update room set users=users+1 where roomNo = roomNo_in and @capacity > @users;
                   if txn_error>0 or ROW_COUNT()<=0  then  
                            set @err = 3;
                            set @msg = CONCAT(@msg,',','未能更新房间人数');
                            ROLLBACK;      
                   end if;
         end if;
         if @err = 0 then       
                   set @msg = CONCAT(@msg,',','已更新房间人数');
                   #插入住房记录
                   insert into roomUser(roomNo,userNo) select roomNo_in,userNo_in from dual where @capacity > @users;
                   if txn_error>0 or ROW_COUNT()<=0  then
                            set @err = 4;
                            set @msg = CONCAT(@msg,',','未记录入住信息');
                            ROLLBACK;               
                   end if;
         end if;
         if @err = 0 then       
                   set @msg = CONCAT(@msg,',','已记录入住信息');
                   COMMIT;         
         end if;
         #输出错误代码与结果消息，错误为0则表示操作成功，否则发生了错误
         select @err err,@msg msg;    
END

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。