title: Locking data in SQL Server using transaction isolation levels
body: <p>I'm starting to develop application that should deal with data access concurrency issues, and I'm having trouble understanding how to properly use transaction isolation levels.</p>
  
  <p>I have the following table called <code>Folders</code> which contains a tree-like folder structure:</p>
  
  <pre><code>+-----------------------------------------------------------------+
  | Id (int) | Name (varchar) | FullPath (varchar) | ParentId (int) |
  |----------+----------------+--------------------+----------------|
  | 1        | 'root1'        | '/root1/'          | NULL           |
  | 2        | 'c1'           | '/root1/c1/'       | 1              |
  | 3        | 'c2'           | '/root1/c1/c2/'    | 2              |
  | 4        | 'root2'        | '/root2/'          | NULL           |
  +----------+----------------+--------------------+----------------+
  </code></pre>
  
  <p>And I'm trying to implement the "Move folder" workflow like this (say, I want to move folder with ID=2 to a new parent with ID=4):</p>
  
  <ol>
  <li>Begin transaction</li>
  <li>Read folder with ID=2 (call it folder2): <code>SELECT * FROM Folders WHERE Id=2</code></li>
  <li>Read folder with ID=4 (call it folder4): <code>SELECT * FROM Folders WHERE Id=4</code></li>
  <li>Update <code>ParentId</code> and <code>FullPath</code> of <code>folder2</code>: <code>UPDATE Folders SET ParentId=folder4.Id, FullPath=folder4.FullPath+folder2.Name+'/' WHERE Id = folder2.Id</code></li>
  <li>Read all subfolders of <code>folder2</code> (call them <code>subfoldersOfFolder2</code>): <code>SELECT * FROM Folders WHERE FullPath LIKE folder2.FullPath + '%'</code></li>
  <li>For each <code>subfolder</code> in <code>subfoldersOfFolder2</code> update <code>FullPath</code> column (query omitted)</li>
  <li>Commit transaction</li>
  </ol>
  
  <p>Obviously, I do not want any other transactions to write (or even read) <code>folder2</code> and <code>subfoldersOfFolder2</code> until my transaction completes. </p>
  
  <p>After reading <a href="https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx" rel="nofollow noreferrer">this article on SQL Server transactions</a> I got the notion that setting isolation level to Serializable at step #1 would help me achieve this. But for some reason this doesn't seem to happen. I tried leaving the transaction open (stopping before step #7), opening another instance of SSMS and doing <code>SELECT * FROM Folders</code>, and the query completes successfully, I can still see the data that was read by the 1st transaction.</p>
  
  <p>Why is this happening? How can I prevent anyone else from reading/writing <code>folder2</code> and <code>subfoldersOfFolder2</code>? I feel like I'm missing something important about how transactions actually lock data.</p>