CODEWITHSUNDEEP
sql-server
Sankar
Sankar

Reputation: 1

Can someone please clarify be on the below explanation

I have a Employee table with 5 records, And i am running the below script. the result of the script is returning a record with EmpID 2. at the same time record is getting deleted. Is this is the right way?

begin transaction A
    select * from Employee where EmpID=2
    begin transaction B
        delete from Employee where EmpID=2
    commit transaction B
commit transaction A

Upvotes: 0

Views: 47

Answers (2)

muhmud
muhmud

Reputation: 4604

If you want to select the row with EmpID = 2, so you can do some work on it and ensure that this doesn't get changed before your delete, use an update lock:

begin transaction A
    select * from Employee with (updlock) where EmpID=2
    delete from Employee where EmpID=2
commit transaction A

You could also use a transaction isolation level higher than read committed, preferably serializable.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425321

You may just use this:

DELETE
FROM    employee
WHERE   empId = 2
OUTPUT  DELETED.*

This will delete the record and output its contents in one statement, atomically.

Upvotes: 2

Related Questions