title: Redshift Concurrent Transactions
body: <p>I'm having issues concurrently writing to a Redshift db. Writing to the db using a single connection works well, but is a little slow, so I am trying to use multiple concurrent connections but it looks like there can only be a single transaction at a time.</p>
  
  <p>I investigated by running the following python script alone, and then running it 4 times simultaneously.</p>
  
  <pre><code>import psycopg2
  import time
  import os
  
  if __name__ == "__main__":
    rds_conn = psycopg2.connect(host="www.host.com", port="5439", dbname='db_name', user='db_user', password='db_pwd')
    cur = rds_conn.cursor()
    with open("tmp/test.query", 'r') as file:
      query = file.read().replace('\n', '')
  
    counter = 0
    start_time = time.time()
    try:
      while True:
        cur.execute(query)
        rds_conn.commit() # first commit location
        print("sent couter: %s" % counter)
        counter += 1
    except KeyboardInterrupt:
      # rds_conn.commit() # secondary commit location
      total_time = time.time() - start_time
      queries_per_sec = counter / total_time
      print("total queries/sec: %s" % queries_per_sec)
  </code></pre>
  
  <p>The <code>test.query</code> file being loaded up is a <a href="http://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-multi-row-inserts.html" rel="nofollow">multi-row insert</a> file ~16.8mb that looks a little like:</p>
  
  <pre><code>insert into category_stage values
  (default, default, default, default),
  (20, default, 'Country', default),
  (21, 'Concerts', 'Rock', default);
  </code></pre>
  
  <p>(Just a lot longer)</p>
  
  <p>The results of the scripts showed:</p>
  
  <pre><code>---------------------------------------------------
  | process count | queries/sec | total queries/sec |
  ---------------------------------------------------
  | 1             | 0.1786      | 0.1786            |
  ---------------------------------------------------
  | 8             | 0.0359      | 0.2872            |
  ---------------------------------------------------
  </code></pre>
  
  <p>...which is far from the increase I'm looking for. When you can see the counter increasing across the scripts there's a clear circular pattern where each waits for the prior script's query to finish.</p>
  
  <p>When the commit is moved from the first commit location to the second commit location (so commits only when the script is interrupted), <strong>only one script advances at a time.</strong> If that isn't a clear indication of some sort of transaction lock, I don't know what is.</p>
  
  <p>As far as I can tell from searching, there's no document that says we can't have concurrent transactions, so what could the problem be? It's crossed my mind that the query size is so large that only one can be performed at a time, but I would have expected Redshift to have much more than ~17mb per transaction.</p>