3

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.

user962800
  • 179
  • 1
  • 4

2 Answers2

1

does a read operation have always precede a write operation

Definitely not.

Consider the following transaction:

START TRANSACTION;
UPDATE employee 
   SET salary = null;
COMMIT;

No read done by the transaction controlling code:

1

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536