How to prevent lock on table when delete from table only ?

Question

I work on sql server 2019 i face issue i can't prevent only lock when delete

first session is

select * from students where studentid between 10 and 50

second session is

update students set className='FirstClass' where studentid between 10 and 50

third session is

delete from students where studentid between 10 and 50

so i need to prevent lock on third session when make delete only

so i need to prevent delete when make delete only

so what i do to make that

Answer 1

Hi @ahmed salah
See this sample:

DELETE FROM LogMessages WHERE LogDate < '20020102';  

Change to this：

DECLARE @done bit = 0;  
WHILE (@done = 0)  
BEGIN  
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';  
    IF @@rowcount < 1000 SET @done = 1;  
END;  

Refer to this doc for more details: Resolve blocking problems caused by lock escalation in SQL Server

Best regards,
LiHong

Answer 2

so i need to prevent lock on third session when make delete only

You can't; that's the way databases works to ensure ACID concept.
https://en.wikipedia.org/wiki/ACID

Answer 3

As Olaf said, you cannot prevent locks. Locks are normal and expected in a multiuser database system.

You have 2 queries trying to affect the same range of records. The server must wait do them one at a time and prevent the 2nd from happening until it is complete.

What exactly is your issue?