InnoDB : Deadlock from one reader and one writer

This sort of has me miffed. One query is doing a select that includes a table that is undergoing a heavy UPDATE, but that update takes under 1/2 a second, and there are no other writers to that table. So I could see how the reader would "wait" until the update completes but I don't understand why this is considered a "deadlock", where neither the update nor the select can proceed.

Here are some facts :

• The table nasd_real has 23,000 rows

• Every 15 seconds, an update happens that updates approx 1500 rows

• This update takes under 1/2 a second, which I know because I can run it from the command line as the data from the source of the update is still there.

• The select is doing an aggregation that joins to nasd_real, the result of that join is inserted int a temp table.

Some settings :

mysql> show global variables like '%tx%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> show global variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

I included the output from show engine innodb status\G below.

If anyone has seen this type of problem under similar conditions, I would most interested what you think!

TIA.

Don

```
------------------------
LATEST DETECTED DEADLOCK
------------------------
120720 6:58:15
*** (1) TRANSACTION:
TRANSACTION 1902337, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
LOCK WAIT 24 lock struct(s), heap size 3112, 302 row lock(s)
MySQL thread id 45032, OS thread handle 0x7f87c4720700, query id 76514460 localhost prog Copying to tmp table
insert into t_open_existing
select p.portfolio_id
, op.position_type_id
, sum(op.position_size * ifnull(nr.last,today.last))
, sum(op.position_size

Due to how MySQL handles replication of INSERT INTO ... SELECT, it will actually do some locking on the rows of the tables involved in the SELECT portion of your query. Here is an older article detailing the issue.

The solution is to use row-based replication and set the transaction level to READ-COMMITTED.

To clarify, the locking of INSERT INTO .. SELECT will happen regardless of if you are using replication at all, it is just part of the internals.

StackExchange Database Administrators Q#21239, answer score: 3