Mysql – InnoDB : Deadlock from one reader and one writer

deadlockinnodbMySQL

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                           
     *(ifnull(nr.last,today.last)-ifnull(nr.prev,today.prev))
                                    * (pt.direction))
from member.portfolio as p
 , today
 , position_types as pt
 , member.open_positions as op
LEFT JOIN nasd_real AS nr
 ON   ( op.symbol_id = nr.symbol_id )

 where p.person_id =  NAME_CONST('p_person_id',1)
    and p.portfolio_type_id =  NAME_CONST('l_portfolio_type_id',1)
 and op.portfolio_id = p.portfolio_id
 and today.symbol_id = op.symbol_id
 and op.load_id <  NAME_CONST('l_today',5177)
 and pt.position_type_id = op.position_type_id
 group by p.portfolio_id, op.position_type_id

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 409609 n bits 320 index `PRIMARY` of table `    prog`.`nasd_real` trx id 1902337 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 190232C, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
57 lock struct(s), heap size 14776, 942 row lock(s)
MySQL thread id 42342, OS thread handle 0x7f87c4567700, query id
  76514371     localhost prog Sending data
update  nasd_real,
            stream_last
 set             nasd_real.last_updated = stream_last.last_updated,
            nasd_real.volume = stream_last.volume,
            nasd_real.last = stream_last.last,
            nasd_real.high = stream_last.high,
            nasd_real.low = stream_last.low
where   nasd_real.symbol_id = stream_last.symbol_id

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 409609 n bits 320 index `PRIMARY` of
 table     `prog`.`nasd_real` trx id 190232C lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 409604 n bits 320 index `PRIMARY` of
table     
`prog`.`nasd_real` trx id 190232C lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

Best Answer

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.

Related Question