Select语句阻止对InnoDB表的读/写操作 [英] Select statement blocks the read/write operation on the InnoDB table

问题描述

我有一个Select查询，该查询在具有超过4百万条记录的事务表上执行.每当执行此查询时，都会观察到该特定事务表上的所有写入和更新操作都被挂起，并且我们开始从Java端获取异常，该异常超出了锁等待超时，请尝试重新启动事务.当前锁定等待超时设置为200秒.我无法理解为什么select语句可以在表上创建此类锁并阻止所有insert/update语句.表存储引擎是InnoDb，主键是自动增量键. MySQL版本是5.1.40. 另外，在执行select语句之前，我不会启动任何事务.

I have a Select query which executes on a transactional table having more than 4 million records. Whenever I execute this query , I observe that all write and update operations on that particular transactional table become suspended and we start getting exceptions from java side that lock wait timeout exceeds , try restarting transaction. Currently lock wait timeout is set to 200 seconds. I am unable to understand that why a select statement can create such locks on the table and block all insert/update statements. The table storage engine is InnoDb and primary key is auto-increment key. The MySQL Version is 5.1.40. Also I m not starting any transaction before executing select statement.

有什么想法吗?

推荐答案

因此，是的，您的SELECT在一个事务中读取锁定了该表的记录，并且触及相同记录的写操作将不得不等待，直到读取事务完成为止(如果遵循两个锁相).

So, yes, your SELECT in one transaction read-locks the records of that table and write operations which touch the same records will have to wait until read transaction completes (if it follows two phase locking).

本文档可能有助于理解innodb锁模型

This document may help understanding innodb locks model

这篇关于Select语句阻止对InnoDB表的读/写操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！