Talking about transactions (db transactions), does a read operation have always precede a write operation?

That's obvious, isn't it?

Thanks in advance

*Of course my question doesn't take into account any specific locking protocol or whatever it's actually related to the following question:

A transaction T is defined by a partial order, termed <, such that: 

if r[x] and w[x] belong to T, then r[x] < w[x]. 

Thanks again.

  • Which book/source is this from? As far as I'm aware (speaking from a practical viewpoint, not an academic one), the key requirement of a transaction is that it must leave the database in a consistent state (the other parts of the ACID properties are handled by the DBMS). I'm not aware of any RDBMSs that require reads to come before writes in transactions. – Simon Righarts Jan 29 '12 at 8:19
  • 1
    What do you mean by 'transaction'? I think it is something different to what I usually mean? – Jack Douglas Jan 29 '12 at 8:47
  • By transaction a I mean a partial order defined over the transaction operations and characterized by the precedence relation <... – user962800 Jan 29 '12 at 10:51
  • @user962800: I am not sure what you are asking. The statement 'A transaction T is defined ...' does not say that a value must be read before it is written. I think the statment makes sense because is is not necessary to read a value after it has been written because you know whaht you have written and the value could not be changed by others within your transaction. – miracle173 Jan 29 '12 at 15:32

does a read operation have always precede a write operation

Definitely not.

Consider the following transaction:

UPDATE employee 
   SET salary = null;

No read done by the transaction controlling code:

| improve this answer | |
  • and where does the value of salary on the 'salary * 1.25' expression comes from? – miracle173 Jan 29 '12 at 15:00
  • @miracle173: you have a point there. It is read when processing an update like that. I changed my example – a_horse_with_no_name Jan 29 '12 at 15:02
  • there are a lot of read operations before the write operations caused by this update. otherwise the database system would not know how many records have to be updated an where the updates have to be written. But I think user962800 is talking about salary value. – miracle173 Jan 29 '12 at 15:28
  • Then I think also all insert statements dont need a read operations before the rite operation. – miracle173 Jan 29 '12 at 15:36
  • If we are talking about the technical implementations, then even an INSERT will need to do reads e.g. to do a lookup on the primary key. – a_horse_with_no_name Jan 29 '12 at 16:09

The answer to that would not be a simple yes or no. It depends on what your need to read. Otherwise, transaction isolation via MVCC would become rather pointless.

For example, in MySQL's InnoDB storage engine you have four levels of transaction isolation:

When it comes to the order of reads and writes

  • READ-UNCOMMITTED and READ-COMMITTED expect reads after writes (aka dirty reads)
  • REPEATABLE-READ would expect reads before writes
  • SERIALIZABLE would simply be nonsequiter (order doesn't matter)

As along as the DBMS (like MySQL, Oracle, PostgreSQL) supports transactions via MVCC, read and write order should not matter. What should really matter is the content of reads coupled with the selected transaction isolation level.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.