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
------------
