0

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?

Esty
  • 1,882
  • 3
  • 17
  • 36
  • 5
    Read it twice. Still not sure what you're trying to *do*, or what you're asking. Could you try to edit your question and add more *context*, a clear *problem* statement, etc. – Damien_The_Unbeliever Aug 26 '15 at 12:24
  • 1
    Use `SET TRANSACTION ISOLATION LEVEL ...` Your requirements would call for something between `REPEATABLE READ` and `SERIALIZABLE`. See the documentation here: https://msdn.microsoft.com/en-us/library/ms173763.aspx – Code Different Aug 26 '15 at 12:27
  • Actually I want to lock a row when transaction begins with read on that row. I don't want anyone read that until transaction commit. – Esty Aug 26 '15 at 14:27
  • 1
    You get to choose what *your* transaction wants/needs - not what other transactions choose to do. If another transaction is running with `NOLOCK` or `READ UNCOMMITTED`, there's nothing *your* code can do to prevent it. – Damien_The_Unbeliever Aug 26 '15 at 14:32
  • What if another transaction simply read the row without NOLOCK or READ UNCOMMITTED? – Esty Aug 26 '15 at 14:58

0 Answers0