Thread: Questions concerning concurrency control in PostgreSQL

I have a few questions concerning concurrency control in PostgreSQL. I shall thank whoever can help me.

Question #1
=========
Assume the following (concurrent) schedule, in which both transactions run in a serializable isolation level:
T1 begin
                                              T2 begin
T1 modifies an item A
                                              T2 reads (selects) A
                                              T2 modifies an item B
T1 reads (selects) B
T1 commits
                                              T2 commits
If I understand correctly then both transactions would be able to commit (as they modified different items). Each would see a snapshot of the database as if it ran alone,

and each would read the initial value of the item it reads (and not its value after the update). But we cannot say that the schedule is equivalent nor to the serial schedule that run T1 first (as in this schedule T2 would read the value of B after it was modified by T1), neither to the schedule that run T2 first (from a symmetric argument concerning item B). So the schedule is not serializable in the sense of the theory of database systems (e.g. Ullman’s Principles of Database Systems book). Am I right?

Question #2
=========
I was not able to understand the difference between all the lock modes, when would a transaction (or the db system) use each lock, and which data structures each lock locks. For example: what is the difference between a ROW SHARE lock mode, and a ROW EXCLUSIVE lock mode. I understand that the former is acquired by a select … for update, while the latter is acquired, for example, by an UPDATE command; but after a transaction issues select … for update it has the opportunity to modify the row, so why do we need two different lock modes? Or what is the difference between SHARE and ACCESS SHARE? Which data structures are being locked by each lock? Why is EXCLUSIVE congruent with ACCESS SHARE?

Question #3
=========
In some places it is said that a transaction that only reads does not lock any table or row, and is never blocked. But if a transaction T1 modifies a row r, and at the same time transaction T2 selects r, then T2 need to wait until T1 finishes (as T1 might have deleted the row, or modified it in a way that would cause T1 not to need it, as the row does not satisfy T2’s WHERE clause). Am I right? On the other hand in order to read a table T2 gets an ACCESS SHARE lock on the table, so it blocks transactions that want to drop the table (and I do not understand why it does not block transactions that want to add/delete/update rows of the table).

I would thank you if you could find the time to help me with those questions.

Thanking you in advance

Yoram Biberamn
Dept. of Computer Science
Hadassah Academic College of Technology
Jerusalem
Israel