Best way to avoid dirty read in SQL server

1.1k views Asked by At

I found by other posts that in SQL server a read can't be locked by another read. So I do as following

BEGIN TRAN
-- I used this update to lock specific row
UPDATE TBL_BALANCE SET TBL_BALANCE.DUMMYCOLUMN = 1 SET WHERE TBL_BALANCE.ACCOUNT = 'XXXXXXXXX'

SELECT BALANCE FROM TBL_BALANCE WHERE TBL_BALANCE.ACCOUNT = 'XXXXXXXXX'
-- Do Some validations over BALANCE
-- Do some update on BALANCE

-- I used this update to lock specific row when next transaction occur by setting DUMMYCOLUMN = 1
UPDATE TBL_BALANCE SET TBL_BALANCE.DUMMYCOLUMN = 0 SET WHERE TBL_BALANCE.ACCOUNT = 'XXXXXXXXX'
COMMIT TRAN

Is there any other efficient way???

UPDATE

I just do those updates on DUMMYCOLUMN so that no other transaction could read BALANCE of ACCOUNT 'XXXXXXXXX' until current transaction commits. My purpose is to read BALANCE of ACCOUNT 'XXXXXXXXX', do some validations and update BALANCE. If I omit the first update then during my transaction another transaction would read BALANCE. I prevent this by locking the row by UPDATE and want to know is there other ways to lock the row without update that row?

0

There are 0 answers