5

say I want to do the following transactions in read committed mode (in postgres).

T1: r(A) -> w(A)
T2: r(A) -> w(A)

If the operations where called in this order:

r1(A)->r2(A)->w1(A)->c1->w2(A)->c2

I would exspect that T2 has to wait at r(A). Because T1 would set an exclusive lock for A at the first read, because it wants to write it later. But with MVCC there are are no read locks?

Now i've got 2 questions:

If I use JDBC to read some data and then execute a separte command for inserting the read data. How does the database know that it has to make an exclusiv lock when it is only reading? Increasing an read lock to a write lock is not allowed in 2PL, as far as I know.

I think my assumtions are wrong... Where does this scenario wait or is one transaction killed? Read uncommitted shouldn't allow lost updates, but I can't see how this can work.

I would be happy if someone could help me. Thanks

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84

3 Answers3

1

I would exspect that T2 has to wait at r(A). Because T1 would set an exclusive lock for A at the first read, because it wants to write it later. But with MVCC there are no read locks?

There are write locks if you specify for update in your select statements. In that case, r2(A) would wait to read if it's trying to lock the same rows as r1(A).

http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html

A deadlock occurs if two transactions start and end up requesting each others already locked rows:

r11(A) -> r22(A) -> r12(A) (same as r22) vs r21(A) (same as r11) -> deadlock
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
1

"But with MVCC there are are no read locks?"

MVCC is a different beast. There are no "locks" in MVCC because in that scenario, the system maintains as many versions of a single row as might be needed by the transactions that are running concurrently. "Former contents" of a row are not "lost by an update" (i.e. physically overwritten and destroyed), and thus making sure that a reader does not get to see "new updates", is addressed by "redirecting" that reader's inquiries to the "former content", which is not locked (hence the term "snapshot isolation"). Note that MVCC, in principle, cannot be applied to updating transactions.

"If I use JDBC to read some data and then execute a separate command for inserting the read data. How does the database know that it has to make an exclusive lock when it is only reading? Increasing an read lock to a write lock is not allowed in 2PL, as far as I know."

You are wrong about 2PL. 2PL means that acquired locks are never released until commit time. It does not mean that an existing lock cannot be strengthened. Incidentally : that is why isolation levels such as "cursor stability" are not 2PL : they do release read locks prior to commit time.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • strict 2PL does actually disallow promoting locks. Otherwise another transaction could read the the originial data, then the first transaction promotes to a write lock and then commites after writing. Now transaction 2 writes and commites. This leaves use with a lost update of transaction 1. If it had request a write lock right away transaction 2 would have had to wait. –  May 26 '11 at 15:58
  • But now I understand MVCC or snapshot isolation. The write sets have to be disjunct. So along you write everything you read it's fine. –  May 26 '11 at 16:01
0

The default transaction mode in PostgreSQL is READ COMMITTED, however READ COMMITTED does not provide the level of serialization that you are looking for.

You are looking for the SERIALIZABLE transaction level. Look at the SET TRANSACTION command after reading PostgreSQL's documentation on Transaction Serialization Levels, specifically the SERIALIZABLE mode. PostgreSQL's MVCC docs are also worth reading.

Cheers.

Sean
  • 9,888
  • 4
  • 40
  • 43
  • Thanks, could you maybe explain what read commited would do with these transactions? I though lost updates are impossible with read commited? –  May 20 '11 at 18:04
  • No, they're very possible. I hate to say RTFM, but I can't explain it better than the docs do. http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED Be sure to read the last two paragraphs of 13.2.1. – Sean May 20 '11 at 20:26
  • You may also want to check out PostgreSQL 9.1 (beta1 now), which has added true serializable transaction isolation level. – sayap May 21 '11 at 01:46
  • As long as I write everything that I read snapshot isolation is fine. When the write sets of transaction are not disjuct one will be discarded. –  May 26 '11 at 16:04