Would this prevent the row from being read during the transaction?

Question

I remember an example where reads in a transaction then writing back the data is not safe because another transaction may read/write to it in the time between. So i would like to check the date and prevent the row from being modified or read until my transaction is finish. Would this do the trick? and are there any sql variants that this will not work on?

update tbl set id=id where date>expire_date and id=@id

Note: date>expire_date happens to be my condition. It could be anything. Would this prevent other transaction from reading the row until i commit or rollback?

Answer 1

It depends on the transaction isolation level you set on your transaction control. There are 4 types of read

READ UNCOMMITTED: this allows the dirty read
READ COMMITTED 
REPEATABLE READ
SERIALIZABLE

for more info, you can check msdn.

Answer 2

You should be able to do this in a normal select using a combination of

HOLDLOCK/ROWLOCK

Answer 3

It very well may work. Different platforms offer different services. For instance, in T-SQL, you can simply set the isolation level of the transaction and, as a result, force a lock to be obtained. I don't know what platform you are using so I cannot answer your question definitively.

Answer 4

In a lot of cases, your UPDATE statement will not prevent other transactions from reading the row.

ziang mentioned transaction isolation levels.

Depending on the isolation level, databases use different types of locking. At the highest level, locking can be divided into two categories:
- pessimistic,
- optimistic

MS SQL 2008, for example, has 6 isolation levels, 4 of them are pessimistic, 2 are optimistic. By default , it uses READ COMMITTED isolation level, which falls into the pessimistic category.

Oracle, on another note, uses optimistic locking by default.

The statement that will lock your record for writing is

SELECT * FROM TBL WITH UPDLOCK WHERE id=@id

From that point on, no other transaction will be able to update your record with id=@id And only transactions running in isolation level READ UNCOMMITTED will be able to read it.

With the default transaction level, READ COMMITTED, no other thansaction will be able to read or write into this record until you either commit or roll back your entire transaction.