title: How to ensure data consistency across multiple related tables with concurrent queries
body: <p>So, my data model is similar to an assignment problem. So, let's assume we have a firm that provides suitable workers for requested jobs.</p>
  
  <p>For now, I have such relations: </p>
  
  <ul>
  <li><code>Customer</code> (id)</li>
  <li><code>Job</code> (id)</li>
  <li><code>Worker</code> (id, available)</li>
  <li><code>Jobs In Progress</code> (customer_id, job_id)</li>
  <li><code>Busy Workers</code> (customer_id, worker_id)</li>
  </ul>
  
  <p>There are many-to-many between <code>Customer</code> and <code>Job</code> and many-to-many between <code>Customer</code> and <code>Worker</code>. This data is like real-time, so it's highly dynamic.</p>
  
  <p>We want to maintain such queries:</p>
  
  <ol>
  <li>Request a worker for a job.</li>
  <li>Return the worker when the job has finished.</li>
  </ol>
  
  <p>This queries requires to read, update, delete and insert data in several tables.</p>
  
  <p>For example, if customer requests for a worker, we have to check whether this customer already exists in the table; whether he already owns a suitable worker in <code>Busy Workers</code>; if no, find a suitable available worker in <code>Worker</code>; check whether such a job already registered in <code>Job</code>. And in the worst case, we have to atomically insert customer in <code>Customer</code>, insert job in <code>Job</code>, insert a corresponding row in <code>Jobs In Progress</code>, decrement <code>Worker.avaiable</code> and insert a row in <code>Busy Workers</code>.</p>
  
  <p>In the second query, we have to do all of this stuff in a reversed order: increment <code>Worker.available</code>, delete the customer if he has no jobs, delete the job if no one customer needs it and so on.</p>
  
  <p>So we have a lot of consistency rules: number of busy workers have to be consistent with <code>Worker.available</code>, a customer has to be present in the table only if he has requested not finished jobs, a job has to be present in the table only if there are no customers with such a job requested.</p>
  
  <p>I read a lot about isolations levels and locking in databases, but I still don't understand how to ensure consistency across multiple tables. It seems like isolation levels don't work because multiple tables are involved and data may become inconsistent between select from two tables. And it seems like locks don't work too, because AFAIK SQL Server can't atomically acquire a lock on multiple tables and therefore data may become inconsistent between locks.</p>
  
  <p>And, actually, I'm looking for a solution or idea of a solution in general, without referencing to a concrete RDBMS, it should be something that applicable one way or another to the most famous RDBMS's like MySQL, PostgreSQL, SQL Server, and Oracle. So it does not have to be a proper solution with examples with all of this RDMS's, maybe some practices, tips or 
  references.</p>
  
  <p>I apologize for my English and thank you in advance.</p>