title: Transaction isolation and reading from multiple tables on SQL Server Express and SQL Server 2005
body: <p>I have a database with a main table (lets call it Owner) and several sub tables with holdings (like Cars, Books etc).</p>
  
  <p>For example:</p>
  
  <ul>
  <li><code>Owner</code> has columns: <code>owner_id, name</code></li>
  <li><code>Cars</code> has columns: <code>owner_id (foreign key), brand</code></li>
  <li><code>Books</code> has columns: <code>owner_id (foreign key), title, author</code></li>
  </ul>
  
  <p>My program should calculate statistics like <em>How many BMW owners also owns a Harry Potter book</em> using various third party libraries. I want to read all rows from all tables at the same time and then do the analysis in non-sql code.</p>
  
  <p>I want to read all tables using separate <code>Select * From X</code> statements. I cannot use one big join since it would return too many rows ((owners * cars * books) instead of (owners + cars + books)). A Union doesn't cut it either since the tables contain different columns of different types.</p>
  
  <p>I have set</p>
  
  <pre><code>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  </code></pre>
  
  <p>but I'm having some issues anyway.</p>
  
  <p>If I stress the database by running two threads, one randomly inserting or deleting and the other reading I sometimes get inconsistent results, like <code>Cars</code> have been deleted between reading <code>Owners</code> and reading the <code>Cars</code> table. </p>
  
  <p>I have a few questions:</p>
  
  <ol>
  <li><p>What's the proper way of preventing modification when reading from multiple tables one by one? No table must be modified until all have been read. </p>
  
  <p>I'm using SQL Server 2005 (on network) and SQL Server 2005 Express (local). Can I explicitly get locks on multiple tables at the same time?</p></li>
  <li><p>If I run against my local SQL Server Express database, I can't make it work no matter what I do. If I run against my networked SQL server 2005 database, I can make it work (with some effort). Does SQL Server Express support transaction isolation level SERIALIZABLE? I believe it should. The differences could be due to a slow network connection but I don't know.</p>
  
  <p>On my local db, I can not prevent modification in between reads. That is, one thread is randomly deleting a random owner (first cars, then books, then owner) or inserting a new owner (insert owner, insert 2 cars, insert 2 books). Another thread is reading using:</p>
  
  <pre><code>Begin Tran
  Select owner_id From Owner
  Select owner_id, brand From Cars
  Select owner_id, title, author From Books
  Commit Tran
  </code></pre>
  
  <p>No matter what I do, sometimes I get an owner with zero cars or zero books. This should never happen since all inserts and deletes are in a single transaction. I seems like the express server doesn't lock Owner, Cars and Books statements at the same time.</p>
  
  <p>On the networked SQL Server 2005, it works fine but it could be because of a slow connection and thus lower probability of simultaneous execution.</p></li>
  <li><p>On my local db, I am starting every transaction with a dummy <code>Select</code> from all tables to prevent deadlocking. I don't understand why this prevents deadlocking but not modification of the tables. This is not necessary on the networked SQL Server 2005.</p></li>
  </ol>
  
  <p>At the moment, I can't tell if I've misunderstood something about transaction isolation or if it's an issue with differences between SQL Server Express and SQL Server 2005. Any help or insights would be greatly appreciated.</p>