CODEWITHSUNDEEP
sql-serverdatabase
ForguesR
ForguesR

Reputation: 3618

UPDATE and SELECT a row in the same transaction

Is it possible to UPDATE a row and then SELECT the updated data from the very same row inside the same transaction without fear of a deadlock?

Upvotes: 7

Views: 4374

Answers (4)

Vinoth K
Vinoth K

Reputation: 106

To SELECT inserted value use the following query

UPDATE SET maths = 20,biology = 21 OUTPUT INSERTED.maths, INSERTED.biology WHERE student_id = 12

To SELECT previous value use

UPDATE SET maths = 20 OUTPUT DELETED.maths WHERE student_id = 12

Upvotes: 0

Juan
Juan

Reputation: 3705

Yes, if you run in the same transaction both statements they shouldn't deadlock between them

If you are afraid of deadlocking with other transactions, you should minimize the locking caused by your transactions, using a indexed WHERE clause in the UPDATE and avoiding SERIALIZABLE isolation level if possible. (ReadCommitted do the trick for you?)

Upvotes: 1

user4650451
user4650451

Reputation:

Yes, that should be fine. A deadlock occurs when two separate SQL sessions try to access the same record at the same time. Because the Update and Select are happening in the same Transaction, it will force them to happen sequentially. However, if multiple sources are running your Transaction, that could cause a deadlock.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use OUTPUT clause to do this.

Update table_name
set col1='some_value'
output inserted.*

Upvotes: 6

Related Questions