UPDATE and SELECT row in one transaction

Question

Is it possible to UPDATE row, and then SELECT updated data from the same row within the same transaction without fear of a shutter?

Answer 1

Use OUTPUT to do this.

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

Answer 2

Yes, if you launched both statements in the same transaction, they should not be looped between them.

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

Answer 3

Yes, that should be good. A deadlock occurs when two separate SQL sessions try to access the same record at the same time. Since Update and Select occur in the same transaction, this will force them to execute sequentially. However, if multiple sources trigger your transaction, it can cause a dead end.

Answer 4

For SELECT to insert a value, use the following query

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

To select the previous value, use

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