title: Locking Mysql Transaction
body: <p>We have a table (say, child) that has a relation to another table (say, parent). In a perfect world, it will always have a parent row, and sometimes a child row. It should never have more than one child row, but it may in the future (so a Unique index is not suitable long-term).</p>
  
  <p>Right now, we use transactions and lock the rows. However, because Mysql locks the rows based to the point in time in which it starts, each transaction (if one starts before the other commits) is able to create their own row. Then, on insert, each insert takes effect and we end up with two rows. I think each row is locking its own row, and without committing, it is hidden to the other thread. Basically, transactions have a chicken and egg type of problem.</p>
  
  <p>How can we enforce a policy of up to one row? We can add a unique index and at the time the transaction commits, it will fail. But then we remove the ability to add multiple rows in the future (when one parent would have two child's), which is problematic.</p>
  
  <p>This has to be solved somehow. I just don't know how personally.</p>
  
  <p><strong>Edit 1: Updated Schema information (I'm using a job schema to represent the problem)</strong></p>
  
  <p>Table: job (the "Parent")
  job_id
  job_title
  job_payment</p>
  
  <p>Table: job_asignment (the "Child")
  job_id
  user_id (assigned worker)
  est_hours
  opt_insurance</p>
  
  <p>Our application is a SaaS-based product that helps manage workflows. We check whether everything necessary is okay before hand (like whether the job is still in the right status, whether the person trying to accept the job was given access, and so on). Then, if that is true, we assign him (insert or update the row in the job_assignment table).</p>
  
  <p>Our problem is that our system takes 2 to 3 seconds for the rest of the assignment to happen (place payment holds, insert the actual row, email the worker that they are assigned, move the status to assigned, and so on). During this time, another user also tries to accept the job, his thread validates everything before (where we check if its still available), and it is. We then start the process on him too, since each thread is a transaction and the changes haven't been committed.</p>
  
  <p>Then, we get two assignment rows. For us, thats bad right now since we only pay one worker.</p>
  
  <p>We would use application locking with temp files or something, but were on a load balanced (HA) environment and cannot guarantee that both users hit the same server.</p>
  
  <p>This seems really rudimentary, but I can't figure out how to solve it. Other than a unique index, the only other way is to highly invest in hardware for the DB and get that window as small as we can.</p>
  
  <p>Does this clarify anything?</p>