Data blocking in SQL Server using transaction isolation levels

Question

I am starting to develop an application that should solve the problems of accessing concurrency data, and I am having problems understanding how to correctly use transaction isolation levels.

I have the following table with a name Foldersthat contains a tree structure of folders:

+-----------------------------------------------------------------+
| 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 (let's say I want to move the folder with ID = 2 to a new parent with id = 4):

• Start transaction
• Read the folder with ID = 2 (call it folder2): SELECT * FROM Folders WHERE Id=2
• Read the folder with ID = 4 (call it folder4): SELECT * FROM Folders WHERE Id=4
• Update ParentIdand FullPath folder2:UPDATE Folders SET ParentId=folder4.Id, FullPath=folder4.FullPath+folder2.Name+'/' WHERE Id = folder2.Id
• Read all subfolders folder2(name them subfoldersOfFolder2):SELECT * FROM Folders WHERE FullPath LIKE folder2.FullPath + '%'
• subfolder subfoldersOfFolder2 update FullPath ( )

, , ( ) folder2 subfoldersOfFolder2 .

SQL Server , Serializable 1 , - , , . ( 7), SSMS SELECT * FROM Folders, , , .

? - / folder2 subfoldersOfFolder2? , - , .

Answer 1

Serializable, , ( SELECT) , , . ... ( ) .

(SELECT) , , SELECT:

SELECT *
FROM dbo.Folders WITH (XLOCK)
WHERE ....

, " ", , WHERE, - , SELECT .. FROM dbo.Folders WITH (XLOCK) .

Answer 2

, , ( : 1:

create table dbo.test(i1 int, a1 varchar(25))
insert into dbo.test values (1,'NY'),(1,'NY'),(1,'NJ'),(2,'NY'),(2,'NY'),(2,'NJ') 
set transaction isolation level serializable
begin transaction
select * from test where i1=1
update dbo.test set i1=3 where a1='NJ'

2,

select * from dbo.test where i1=1 

....

try catch , . .

Answer 3

- , . , .