SQL transaction: Delete between two selects possible

data synchronizationlockingtransaction

I have two SELECT statements within a transaction (repeatable read)

Select @firstItem =id from myTable Where ....

–Do some more magic, so I can't concat the the two queries!!!!

and

Select * from myTable where parent=@firstItem

Is it possible that the children (second query) of the parent(first query) are deleted (from another transaction) after the initial/first select?

How can I prevent this with locks?

Best Answer

Is it possible that the children (second query) of the parent(first query) are deleted (from another transaction) after the initial/first select?

Certainly it is possible.

When you execute your first query only the result row of the first query has S-lock (I suppose your table has an index on id).

If you want to lock the rows where this id is a parent, you should just do this select in the same query:

select * 
from myTable 
where parent = (
    select id 
    from myTable 
    where ...);

This way you lock that id and all the rows where parent = this id so they cannot be deleted.

Note that only these rows are locked because your TIL is repeatable read, this means that if during the duration of this transaction other rows with this parent are inserted they can also be deleted.

To prevent insertions/deletions of the new rows with this parent you should use serializable TIL.


Here is my repro: The first session(56) creates the table and fills it with id 1..10000 and then adds random parents from the same range 1..10000.

Then I search for most "popular" parent with max count of corrensonding childs, and in session 54 I try to delete one of the child, the query is blocked.

The "central" window shows you the locks held, I used it twice: first to see what my first session locks, second time to see what the second session is waiting for. First session has 1+ 6 locks 1 on parent row and 6 on child rows that you see in the output of the first query, the second session waits on conversion from update lock to exclusive lock on the row it wanted to delete. The session is "hanging" until rollback or commit.

Here is the picture:

enter image description here

if object_id('myTable') is not null drop table myTable;
create table myTable(id int identity not null primary key clustered, 
                     parent int, 
                     col varchar(10) not null);

alter table myTable 
add constraint FK_paerent_id foreign key (parent) references myTable(id);

alter table myTable add constraint UQ_myTable_col unique(col);

create index ix_parent on myTable(parent);

insert into myTable with(tablock) (col) 
select top 10000 cast(row_number() over(order by getdate()) as varchar(10)) as col
from sys.columns c1 cross join sys.columns c2;

update myTable
set parent = abs(checksum(newid())) % 10000 + 1;

select parent,
       count(*) as cnt
from myTable 
group by parent
having count(*) > 1
order by cnt desc;

set transaction isolation level repeatable read;

begin tran

select id 
from myTable 
where parent = (
    select id 
    from myTable 
    where col = '481');

--rollback