title: SQL Deadlock with Python Data Insert
body: <p>I'm currently trying to build a database interface with python to store stock data. This data is in the form of a tuple list with each element consisting of "date, open, high, low, close, volume. <code>date</code> represents a UNIX timestamp and has to be unique in combination with the ticker symbol in the database. Below is an example of a typically processed output (<code>company_stock</code>):</p>
  
  <pre><code> [(1489780560, 'NYSE:F', 12.5, 12.505, 12.49, 12.495, 567726),
   (1489780620, 'NYSE:F', 12.495, 12.5, 12.48, 12.48, 832487),
   (1489780680, 'NYSE:F', 12.485, 12.49, 12.47, 12.475, 649818),
   (1489780740, 'NYSE:F', 12.475, 12.48, 12.47, 12.47, 700579),
   (1489780800, 'NYSE:F', 12.47, 12.48, 12.47, 12.48, 567798)]
  </code></pre>
  
  <p>I'm using the <code>pymysql</code> package to insert this list into a local MySQL database (Version 5.5). While the code runs through and the values get inserted, the database will crash - or rather stop - after reaching about ~250k rows. Since the relevant This is the export part of the stock data processing function which gets called about once every 20 seconds and inserts about 400 values.</p>
  
  <pre><code># SQL Export
  def tosql(company_stock, ticker, interval, amount_period, period):
      try:
          conn = pymysql.connect(host = "localhost", user = "root", 
                                 passwd = "pw", db = "db", charset = "utf8",
                                 autocommit = True, 
                                 cursorclass = pymysql.cursors.DictCursor)
          cur = conn.cursor()
  
          # To temp table
          query = "INSERT INTO stockdata_import "
          query += "(date, tickersymbol, open, high, low, close, volume)"
          query += "VALUES (%s, %s, %s, %s, %s, %s, %s)"
          cur.executemany(query, company_stock)
  
          # Duplicate Check with temp table and existing database storage
          query = "INSERT INTO stockdata (date, tickersymbol, open, high, low, close, volume) "
          query += "SELECT i.date, i.tickersymbol, i.open, i.high, i.low, "
          query += "i.close, i.volume FROM stockdata_import i "
          query += "WHERE NOT EXISTS(SELECT dv.date, dv.tickersymbol FROM "
          query += "stockdata dv WHERE dv.date = i.date "
          query += "AND dv.tickersymbol = i.tickersymbol)"
          cur.execute(query)
  
          print(": ".join([datetime.now().strftime("%d.%m.%Y %H:%M:%S"), 
                           "Data stored in Vault. Ticker", str(ticker),
                           "Interval", str(interval), 
                           "Last", str(amount_period), str(period)]))
      finally:
          # Clear temp import table and close connection
          query = "DELETE from stockdata_import"
          cur.execute(query)
          cur.close()
          conn.close()
  </code></pre>
  
  <p>I suspect that the check for already existent values takes too long as the database grows and eventually breaks down due to the lock of the tables (?) while checking for uniqueness of the date/ticker combination. Since I expect this database to grow rather fast (about 1 million rows per week) it seems that a different solution is required to ensure that there is only one date/ticker pair. This is the SQL <code>CREATE</code> statement for the import table (the real table with which it gets compared looks the same):</p>
  
  <pre><code>CREATE TABLE stockdata_import (id_stock_imp BIGINT(12) NOT NULL AUTO_INCREMENT,
               date INT(10),
               tickersymbol VARCHAR(16),
               open FLOAT(12,4),
               high FLOAT(12,4),
               low FLOAT(12,4),
               close FLOAT(12,4),
               volume INT(12),
               crawled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
               PRIMARY KEY(id_stock_imp));
  </code></pre>
  
  <p>I have already looked into setting a constraint for the <code>date</code>/<code>tickersymbol</code> pair and to handle upcoming exceptions in python, but my research so far suggested that this would be even slower plus I am not even sure if this will work with the bulk insert of the <code>pymysql</code> cursor function <code>executemany(query, data)</code>.</p>
  
  <p>Context information:</p>
  
  <ul>
  <li>The SQL export shown above is the final part of a python script handling the stock data response. This script, in turn, gets called by another script which is timed by a crontab to run at a specific time each day.</li>
  <li>Once the crontab starts the control script, this will call the subscript about 500 times with a sleep time of about 20-25 seconds between each run.</li>
  <li>The error which I see in the logs is: <em>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</em></li>
  </ul>
  
  <p>Questions:</p>
  
  <ol>
  <li>How can I optimize the query or alter the storage table to ensure uniqueness for a given <code>date</code>/<code>ticker</code> combination?</li>
  <li>Is this even the problem or do I fail to see some other problem here?</li>
  <li>Any further advice is also welcome.</li>
  </ol>