Locking data in SQL Server using transaction isolation levels

Viewed 301

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.

I have the following table called Folders which contains a tree-like folder structure:

+-----------------------------------------------------------------+
| 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           |
+----------+----------------+--------------------+----------------+

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):

  1. Begin transaction
  2. Read folder with ID=2 (call it folder2): SELECT * FROM Folders WHERE Id=2
  3. Read folder with ID=4 (call it folder4): SELECT * FROM Folders WHERE Id=4
  4. Update ParentId and FullPath of folder2: UPDATE Folders SET ParentId=folder4.Id, FullPath=folder4.FullPath+folder2.Name+'/' WHERE Id = folder2.Id
  5. Read all subfolders of folder2 (call them subfoldersOfFolder2): SELECT * FROM Folders WHERE FullPath LIKE folder2.FullPath + '%'
  6. For each subfolder in subfoldersOfFolder2 update FullPath column (query omitted)
  7. Commit transaction

Obviously, I do not want any other transactions to write (or even read) folder2 and subfoldersOfFolder2 until my transaction completes.

After reading this article on SQL Server transactions 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 SELECT * FROM Folders, and the query completes successfully, I can still see the data that was read by the 1st transaction.

Why is this happening? How can I prevent anyone else from reading/writing folder2 and subfoldersOfFolder2? I feel like I'm missing something important about how transactions actually lock data.

3 Answers
Related