title: postgresql - script that uses transaction blocks fails to create all records
body: <p><strong>Background information</strong></p>
  
  <p>I am trying to understand how postgresql well enough so that I can ensure that my web app will not fail when users are simultaneously trying to update the same record. </p>
  
  <p>For testing purposes, I have created two scripts - one script creates / defines the transaction block and the second script simulates load (and attempts to create conflicts) by calling script 1. </p>
  
  <p><strong>Code</strong></p>
  
  <p>Here's what script 1 looks like: </p>
  
  <blockquote>
    <p><a href="http://pastebin.com/6BKyx1dW" rel="nofollow">http://pastebin.com/6BKyx1dW</a></p>
  </blockquote>
  
  <p>And here's what script two looks like: </p>
  
  <blockquote>
    <p><a href="http://pastebin.com/cHUhCBL2" rel="nofollow">http://pastebin.com/cHUhCBL2</a></p>
  </blockquote>
  
  <p>To simulate load on the database and to test for locking issues, I'm calling script 2 from two different commandline windows on my server.  I pass in two different sets of parameters so that when i'm analyzing the results in the database i can see which session created which record. </p>
  
  <p><strong>Problem</strong></p>
  
  <p>When I query the database to count how many records each instance of the script created, I am not consistently getting 200 each. 
  I've captured the results from each instance of the script to see if there were any roll backs documented but there are none. 
  So I have two theories. </p>
  
  <ol>
  <li>The server where these scripts are being executed is not robust enough and so the requests aren't making it to the database server...</li>
  <li>The database server is silently aborting transactions. </li>
  </ol>
  
  <p>To eliminate theory 1, I'm going to set up two different servers and run the script once from each server, instead of opening up 2 commandlines on 1 server. 
  If the number of records created increases... I guess that'll tell me that performance on the current server is an issue. (the "server" i'm currently running the scripts on is just a glorified desktop... so it could very well be the issue).</p>
  
  <p>Regarding theory number 2, I've been trying to read and understand <a href="http://www.postgresql.org/docs/current/static/explicit-locking.html" rel="nofollow">http://www.postgresql.org/docs/current/static/explicit-locking.html</a>
  but since I'm not a database expert, it's taking me a little while to digest everything. 
  I do know that with MS SQL Server, if a record is locked by transaction A, transaction B will wait indefinitely until A is finished. 
  With SQLLite, out of the box, transaction B dies.  But you can specify the number of milliseconds to wait before retrying. </p>
  
  <p>The last paragraph in the postgresql documentation I've listed above says that postgresql will also wait indefinitely for conflicting locks to be released... but I'm not 100% sure that I'm not messing something up in my sql code.</p>
  
  <p>So my questions are as follows: </p>
  
  <ol>
  <li>am i doing anything obviously wrong in my sql code? </li>
  <li>how can i test the sql side of things to see what locks are being used / what's happening under the hood? </li>
  </ol>
  
  <p><strong>EDIT 1</strong></p>
  
  <p>I ran the script again from 2 separate machines.  and machine 1 successfully created 122 records and machine 2 created 183.</p>