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