Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
------------
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
How does "replication" factor into this? I am not doing any replication right now. Are you saying : replicate the table nasd_real, and do a select on that? I am simply trying here to derive an approx portfolio value during the trading day. therefore, I just need really a snapshot of what the values are in nasd_real. I thought Repeatable-Read this is exactly what it was for. I will do more DD, but it seems to be READ-COMMITTED is more strict. but perhaps what you are saying is, the locking will be such that Deadlock wont result. Excuse the long note! any insight appreciated. –  Don Wool Jul 20 '12 at 19:58
    
One more sort of question. What impact would it have if I got rid of INSERT INTO and just ran a straight select on nasd_real and did the summation that is trying to be done (and one reason for INSERT INTO) in the client? perhaps INSERT INTO should simply be avoided in cases when the table being selected from is under a heavy UPDATE load. –  Don Wool Jul 20 '12 at 20:01
    
It doesn't matter if you're using replication at all. You could enable innodb_locks_unsafe_for_binlog but I recommend against it (which is why I didn't put it as part of the question). Read through the description of that link. You can set the transaction level to read-committed for the session only and not affect the global server, and the variable cannot be changed without a server restart. –  Derek Downey Jul 20 '12 at 20:09
    
ok I think I get what you are saying now. I could try this globally : [mysqld] binlog_format=row tx_isolation=READ-COMMITTED –  Don Wool Jul 20 '12 at 20:47
    
I wouldn't put tx_isolation in the my.cnf file. I would do that on a per-session basis –  Derek Downey Jul 20 '12 at 20:52
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.