Geek asks and answers

Read uncommitted mvcc database

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

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

If the operations called in this order:

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

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

Now I have 2 questions:

If I use JDBC to read some data, and then run the separte command to insert the read data. How does the database know that it should make an exclusive lock when it is only reading? As far as I know, increasing read lock write lock in 2PL is unacceptable.

I think my assumptions are wrong ... Where is this scenario waiting or is one transaction killed? Reading uncommitted should not allow for lost updates, but I don't see how this might work.

I would be glad if someone could help me. Thanks

+3
source share
3 answers

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

There are write locks if you specify for updatein your select statements. In this case, r2 (A) will wait to read if it tries to lock the same lines as r1 (A).

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

, , :

r11(A) -> r22(A) -> r12(A) (same as r22) vs r21(A) (same as r11) -> deadlock
0

" MVCC ?"

MVCC - . MVCC "", , , . " " " " (.. ), , , , " ", "" " ", ( " " ). , MVCC .

" JDBC , . , , ? , 2PL."

2PL. 2PL , . , . , , " ", 2PL: .

+1

PostgreSQL READ COMMITTED, READ COMMITTED , .

You are looking for transaction level SERIALIZABLE. Look at the SET TRANSACTION command after reading the PostgreSQL documentation on Transaction Serialization Levels , namely SERIALIZABLE mode . PostgreSQL MVCC docs also deserve attention.

Greetings.

0
source

More articles:


All Articles