1

I have a django application using MySQL and I'm currently having an issue debugging a deadlock (i am relatively new to SQL) that is happening sporadically (it wont happen for an hour or two and then all the sudden I will get 10 deadlocks in a row). My application is streaming in realtime equity information on approx. 600 securities and then for each security updating about 50 various calculations.

This appears to be the chunk of code that is causing the issue:

cursor.execute('UPDATE position_mgmt_securities '
             'INNER JOIN position_mgmt_issuers ON position_mgmt_securities.issuer_id = position_mgmt_issuers.id '
             'INNER JOIN position_mgmt_positions ON position_mgmt_securities.id = position_mgmt_positions.security_id '
             'SET moneyness_pct = CONVERT(underlying_price / strike_price - 1, DECIMAL(12,5)) '
             'WHERE (position_mgmt_securities.issuer_id IN '
             '(SELECT id '
             'FROM (SELECT * FROM position_mgmt_issuers) AS x '
             'WHERE x.ticker = "{0}") AND position_mgmt_positions.close_date IS NULL AND position_mgmt_securities.security_type_id = "ECO")'.format(ticker.replace(' EQUITY', '')))

When running SHOW ENGINE INNODB STATUS i get the following deadlock information. 

*** (1) TRANSACTION:
TRANSACTION 168976, ACTIVE 0 sec fetching rows
mysql tables in use 4, locked 4
LOCK WAIT 12 lock struct(s), heap size 1136, 265 row lock(s), undo log entries 2
MySQL thread id 58, OS thread handle 11068, query id 721547 localhost ::1 root Sending data
UPDATE position_mgmt_securities INNER JOIN position_mgmt_issuers ON position_mgmt_securities.issuer_id = position_mgmt_issuers.id INNER JOIN position_mgmt_positions ON position_mgmt_securities.id = position_mgmt_positions.security_id SET moneyness_pct = CONVERT(strike_price / underlying_price - 1, DECIMAL(12,5)) WHERE (position_mgmt_securities.issuer_id IN (SELECT id FROM (SELECT * FROM position_mgmt_issuers) AS x WHERE x.ticker = "TXT US") AND position_mgmt_positions.close_date IS NULL AND position_mgmt_securities.security_type_id = "EPO")
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 17 n bits 112 index PRIMARY of table `pyfmodel`.`position_mgmt_issuers` trx id 168976 lock mode S waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 80000100; asc     ;;
 1: len 6; hex 000000029412; asc       ;;
 2: len 7; hex 01000000d7141e; asc        ;;
 3: len 7; hex 49535247205553; asc ISRG US;;
 4: len 22; hex 494e5455495449564520535552474943414c20494e43; asc INTUITIVE SURGICAL INC;;
 5: len 8; hex 80000001e7006978; asc       ix;;
 6: len 3; hex 555344; asc USD;;
 7: len 6; hex 333531303130; asc 351010;;
 8: len 2; hex 3335; asc 35;;
 9: len 3; hex 8fc6f2; asc    ;;
 10: SQL NULL;
 11: len 5; hex 800100ae44; asc     D;;
 12: len 1; hex 80; asc  ;;
 13: SQL NULL;
 14: len 30; hex 496e7475697469766520537572676963616c2c20496e632e206465736967; asc Intuitive Surgical, Inc. desig; (total 308 bytes);
 15: SQL NULL;
 16: SQL NULL;
 17: len 8; hex 99a31c9b9d000000; asc         ;;
 18: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 168978, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4548
mysql tables in use 4, locked 4
18 lock struct(s), heap size 1136, 467 row lock(s), undo log entries 2
MySQL thread id 59, OS thread handle 24600, query id 721549 localhost ::1 root Sending data
UPDATE position_mgmt_securities INNER JOIN position_mgmt_issuers ON position_mgmt_securities.issuer_id = position_mgmt_issuers.id INNER JOIN position_mgmt_positions ON position_mgmt_securities.id = position_mgmt_positions.security_id SET moneyness_pct = CONVERT(strike_price / underlying_price - 1, DECIMAL(12,5)) WHERE (position_mgmt_securities.issuer_id IN (SELECT id FROM (SELECT * FROM position_mgmt_issuers) AS x WHERE x.ticker = "ISRG US") AND position_mgmt_positions.close_date IS NULL AND position_mgmt_securities.security_type_id = "EPO")
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 48 page no 17 n bits 112 index PRIMARY of table `pyfmodel`.`position_mgmt_issuers` trx id 168978 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 80000100; asc     ;;
 1: len 6; hex 000000029412; asc       ;;
 2: len 7; hex 01000000d7141e; asc        ;;
 3: len 7; hex 49535247205553; asc ISRG US;;
 4: len 22; hex 494e5455495449564520535552474943414c20494e43; asc INTUITIVE SURGICAL INC;;
 5: len 8; hex 80000001e7006978; asc       ix;;
 6: len 3; hex 555344; asc USD;;
 7: len 6; hex 333531303130; asc 351010;;
 8: len 2; hex 3335; asc 35;;
 9: len 3; hex 8fc6f2; asc    ;;
 10: SQL NULL;
 11: len 5; hex 800100ae44; asc     D;;
 12: len 1; hex 80; asc  ;;
 13: SQL NULL;
 14: len 30; hex 496e7475697469766520537572676963616c2c20496e632e206465736967; asc Intuitive Surgical, Inc. desig; (total 308 bytes);
 15: SQL NULL;
 16: SQL NULL;
 17: len 8; hex 99a31c9b9d000000; asc         ;;
 18: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 22 n bits 112 index PRIMARY of table `pyfmodel`.`position_mgmt_issuers` trx id 168978 lock mode S waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 800001c5; asc     ;;
 1: len 6; hex 000000029410; asc       ;;
 2: len 7; hex 02000001b81635; asc       5;;
 3: len 6; hex 545854205553; asc TXT US;;
 4: len 11; hex 54455854524f4e20494e43; asc TEXTRON INC;;
 5: len 8; hex 80000000310036b0; asc     1 6 ;;
 6: len 3; hex 555344; asc USD;;
 7: len 6; hex 323031303130; asc 201010;;
 8: len 2; hex 3230; asc 20;;
 9: len 3; hex 8fc6f2; asc    ;;
 10: len 5; hex 8000003fa5; asc    ? ;;
 11: len 5; hex 8001003e55; asc    >U;;
 12: len 1; hex 80; asc  ;;
 13: SQL NULL;
 14: len 30; hex 54657874726f6e20496e632e206973206120676c6f62616c2c206d756c74; asc Textron Inc. is a global, mult; (total 345 bytes);
 15: SQL NULL;
 16: SQL NULL;
 17: len 8; hex 99a31c9bb4000000; asc         ;;
 18: len 3; hex 8fc6cd; asc    ;;
Kilgore
  • 65
  • 9
  • Can you identify which processes are locking the records? Try something like https://www.dbrnd.com/2016/02/mysql-script-to-identify-the-locks-and-blocking-transactions/ – Dave Stokes May 14 '19 at 14:36
  • I will strongly suggest you to remove select and inner joins from UPDATE statements. Instead of that, select the rows in advance and update them after that. This will simplify your query and enable you to debug easier – Dilyan Trayanov May 14 '19 at 14:40
  • @DaveStokes it looks like that table is depricated in 8.0, so if i try to run ```SELECT * FROM performance_schema.data_lock_waits``` it returns an empty table, my issue is there is so much data being updated and it is happening so quickly, I am not sure what will cause a given transaction to deadlock every couple hours – Kilgore May 14 '19 at 14:45
  • @DilyanTrayanov Sorry, I am pretty new to SQL. How would i restructure my above query to accomplish what you are saying? – Kilgore May 14 '19 at 14:46
  • @Kilgore Basically you don't need INNER JOINS in the outer query, you can limit what you update in the WHERE clause. If you need to be sure that certain records exists in the related tables put the INNER JOINS in the WHERE clause. This will make the query a lot cleaner – Dilyan Trayanov May 14 '19 at 14:58

0 Answers0