查询中的 Exists 语句居然会锁行？？

小灰狼 2017-06-29 11:40:08
一个存储过程如下：
Delimiter ;

Drop Procedure If Exists `spTest1`;

Delimiter ;;

Create Procedure `spTest1`(

	p_DeviceId Int Unsigned

) Begin 

	If Exists(Select * From tbDevice Where Id = 1) Then 

		Select Now(); -- 因为 if 语句里必须要有至少一条语句，这里随便执行一条语句

	End If;

End ;;



Delimiter;


在 Java 程序中调用这个过程，Java中的连接设置为不自动提交，调用过程时传入参数为1，数据表中有 Id = 1 的这一行
则当调用完这个过程后，并且在数据库事务提交之前，在 navicat 中执行 update tbDevice Set ..... Where Id = 1 这条语句时，更新语句会被阻塞，直到我在java中把事务提交或者关闭才会执行完成更新语句。

但是，Exists 语句不是只进行了查询吗，怎么会锁行呢？
...全文
465 19 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2017-07-04
  • 打赏
  • 举报
 回复
5.7.18
小灰狼 2017-07-03
  • 打赏
  • 举报
 回复
引用 15 楼 zjcxc 的回复:
测试了一下，看起来不是 EXISTS 的问题 把 EXISTS 改成 if (Select 1 From tbDevice Where Id = 1)=1 一样会锁 查了一下官网文档也没找到相关说明，估计是有特殊是处理吧 要避免的话，可以用类似 select case when exists(Select * From tbDevice Where Id = 1) then 1 else 0 end into @x； if @x=1 这样的判断，select 不会出现这种自动加锁的情况（注意不要用 set ，用 set 和 if 一样，会自动加锁）
我改用了 

Declare v_Cnt Int Unsigned;
Select Count(Id) Into v_cnt From tbDevice Where Id = p_DeviceId;
If v_Cnt Is Not Null And v_Cnt > 0 Then 
    -- ......
End If;
这样写没有问题 其实问题也不大，是因为有一个程序员没有及时提交事务引起的，而正常逻辑是事务必须及时提交，但就是对这种情况比较奇怪而已，毕竟没有对数据表进行过写操作，居然就给锁了
zjcxc 2017-07-03
  • 打赏
  • 举报
 回复
测试了一下，看起来不是 EXISTS 的问题 把 EXISTS 改成 if (Select 1 From tbDevice Where Id = 1)=1 一样会锁 查了一下官网文档也没找到相关说明，估计是有特殊是处理吧 要避免的话，可以用类似 select case when exists(Select * From tbDevice Where Id = 1) then 1 else 0 end into @x； if @x=1 这样的判断，select 不会出现这种自动加锁的情况（注意不要用 set ，用 set 和 if 一样，会自动加锁）
zjcxc 2017-07-03
  • 打赏
  • 举报
 回复
从这个信息看，确实加锁了，真神奇
小灰狼 2017-07-03
  • 打赏
  • 举报
 回复
引用 11 楼 zjcxc 的回复:
查 block 了么？ 查了的话把信息给出来
mysql> select * from information_schema.INNODB_TRX \G;
*************************** 1. row ***************************
                    trx_id: 23813821
                 trx_state: LOCK WAIT
               trx_started: 2017-07-03 08:59:34
     trx_requested_lock_id: 23813821:19567:4:315
          trx_wait_started: 2017-07-03 08:59:34
                trx_weight: 2
       trx_mysql_thread_id: 93129
                 trx_query: Update `tbDevice` Set `DeviceSn` = '998' Where `Id` = 1
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 23813820
                 trx_state: RUNNING
               trx_started: 2017-07-03 08:59:06
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 93145
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select * from information_schema.INNODB_LOCKS \G;
*************************** 1. row ***************************
    lock_id: 23813821:19567:4:315
lock_trx_id: 23813821
  lock_mode: X
  lock_type: RECORD
 lock_table: `DSPM_Test`.`tbDevice`
 lock_index: PRIMARY
 lock_space: 19567
  lock_page: 4
   lock_rec: 315
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 23813820:19567:4:315
lock_trx_id: 23813820
  lock_mode: S
  lock_type: RECORD
 lock_table: `DSPM_Test`.`tbDevice`
 lock_index: PRIMARY
 lock_space: 19567
  lock_page: 4
   lock_rec: 315
  lock_data: 1
2 rows in set (0.00 sec)

ERROR:
No query specified
小灰狼 2017-07-03
  • 打赏
  • 举报
 回复
引用 17 楼 zjcxc 的回复:
selct 是没问题，你把语句放到 if 或 set 中就锁了，估计是这类语句有特殊处理，不然就是 BUG
你用的是什么版本的 MySQL？ 我用的是 5.6.21
zjcxc 2017-07-03
  • 打赏
  • 举报
 回复
selct 是没问题，你把语句放到 if 或 set 中就锁了，估计是这类语句有特殊处理，不然就是 BUG
小灰狼 2017-06-30
  • 打赏
  • 举报
 回复
又是一个奇怪现象 如果存储过程里是 If Exists(Select * From tbDevice Where Id In (1, 2, 3, 4, 5)) Then Select Now(); End If; 则锁住的行是 Id = 1 的那条记录，其它记录都不会被锁
小灰狼 2017-06-30
  • 打赏
  • 举报
 回复
引用 4 楼 zjcxc 的回复:
你是在下面这个时候查询的 block 信息，如果是，那么既然被 block，就应该有信息（当然，这个是 innodb 的，如果你的表不是 innodb ，查不到正常） 在数据库事务提交之前，在 navicat 中执行 update tbDevice Set ..... Where Id = 1 这条语句时，更新语句会被阻塞
哦，明白了，是要在别的线程执行 Update tbDevice 语句之后，系统表中才会查到锁行记录
zjcxc 2017-06-30
  • 打赏
  • 举报
 回复
你是在下面这个时候查询的 block 信息，如果是，那么既然被 block，就应该有信息（当然，这个是 innodb 的，如果你的表不是 innodb ，查不到正常） 在数据库事务提交之前，在 navicat 中执行 update tbDevice Set ..... Where Id = 1 这条语句时，更新语句会被阻塞
LongRui888 2017-06-30
  • 打赏
  • 举报
 回复
引用 10 楼 hemowolf 的回复:
[quote=引用 8 楼 yupeigu 的回复:] 你用的存储引擎是 innodb吗
是的[/quote] 那这个可能就和事务的隔离级别有关了。
zjcxc 2017-06-30
  • 打赏
  • 举报
 回复
查 block 了么？ 查了的话把信息给出来
小灰狼 2017-06-30
  • 打赏
  • 举报
 回复
引用 8 楼 yupeigu 的回复:
你用的存储引擎是 innodb吗
是的
小灰狼 2017-06-30
  • 打赏
  • 举报
 回复
引用 7 楼 zjcxc 的回复:
exists 的意思是查到一条满足条件的就返回了，所以只会锁一条是对的
嗯，确实如此 但是我的问题是，为什么Exists判断会锁行？
LongRui888 2017-06-30
  • 打赏
  • 举报
 回复
你用的存储引擎是 innodb吗
zjcxc 2017-06-30
  • 打赏
  • 举报
 回复
exists 的意思是查到一条满足条件的就返回了，所以只会锁一条是对的
小灰狼 2017-06-29
  • 打赏
  • 举报
 回复
查询的Java测试代码如下： 
public class Test {

	public static void main(String[] args) throws Exception {
		Connection conn = null;
		CallableStatement cmd = null;
		ResultSet rs = null;
		String url = "jdbc:mysql://192.168.162.30:3306/DSPM_Test?autoReconnect=true&useUnicode=true&characterEncoding=UTF8";
		String username = "root";
		String password = "East300376";
		Reader reader = null;
		BufferedReader breader = null;
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, password);
			conn.setAutoCommit(false);
			cmd = conn.prepareCall("{call spTest1(?)}");
			cmd.setInt(1, 1);
			
			cmd.execute();
			
			rs = cmd.getResultSet();
			if(rs != null && rs.next()){
				ResultSetMetaData meta = rs.getMetaData();
				for(int i=1; i<=meta.getColumnCount(); i++){
					System.out.println(meta.getColumnLabel(i) + "\t" + rs.getObject(i));
				}
			}
			
			reader = new InputStreamReader(System.in);
			breader = new BufferedReader(reader);
			
			System.out.println("Press Enter to continue.");
			breader.readLine();
			conn.commit();
			
			System.out.println("Press Enter to continue.");
			breader.readLine();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			DisposeUtil.safeClose(rs);
			DisposeUtil.safeClose(cmd);
			DisposeUtil.safeClose(conn);
		}
	}
}
小灰狼 2017-06-29
  • 打赏
  • 举报
 回复
引用 2 楼 rucypli 的回复:
监控一下information_schema.INNODB_LOCKS这个表和INNODB_TRX表 一个是事务表 一个是锁表
mysql>
mysql> select * from information_schema.INNODB_LOCKS \G;
Empty set (0.00 sec)

ERROR:
No query specified

mysql> select * from information_schema.INNODB_TRX \G;
*************************** 1. row ***************************
                    trx_id: 23786741
                 trx_state: RUNNING
               trx_started: 2017-06-29 16:11:31
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 87505
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

ERROR:
No query specified
从系统表上看，并没有锁表啊
rucypli 2017-06-29
  • 打赏
  • 举报
 回复
监控一下information_schema.INNODB_LOCKS这个表和INNODB_TRX表 一个是事务表 一个是锁表
mybatis mysql 行锁_第六节：select语句的锁行和锁表功能
此书从数据访问存储层为入口，将延伸到分布式、高并发方面的关键技术。希望这本书能对每一个做Java Web开发的读者带来帮助，也欢迎大家提出更多...1、插入测试数据drop table if exists person;CREATE TABLE person...
Oracle执行exists优化,Oracle之sql语句优化
项目中尽管使用了orm映射，但Oracle系统的优化还是非常有价值的，这里从sql语句的角度对经常使用的语句做下总结。1、from字段中的优化：Oracle安照从右到左的顺序载入表数据，应该把能够排除数据最多的表放到后面...
Mysql子查询关键字的使用（exists）
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，...外层查询进行查询时，外层查询会一行一行执行，第一行执行时，判断子查询是否为true，为true则此行数据被查出，接着第二行查询。...
MySQL 数据库下DELETE、UPDATE 子查询的锁机制解析与优化
在日常的工作之中，数据库开发与维护人员避免不了与 in/exists、not in/not exists 子查询打交道，接触过的人可能知道 in/exists、not in/not exists 相关子查询会使 SELECT 查询变慢，没有 join 连接效率，却不...
SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例
接触过的人可能知道 in/exists、not in/not exists 相关子查询会使 SELECT 查询变慢，没有 join 连接效率，却不知道 DELETE、UPDATE 下的子查询却可能导致更严重的锁问题，直接导致 MySQL InnoDB 行锁机制失效，锁...
MySQL

56,864

社区成员

56,753

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章