Re: A few beginner's questions concerning concurrency control

On Tue, Jun 29, 2004 at 11:55:50 +0200,
Yoram Biberman <yoramb(at)hadassah-col(dot)ac(dot)il> wrote:
> 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?

Your example is not serializable. The main problem case for this is when
people try to select the maximum value in a table and use a higher value
in a newly inserted record. You need to lock the table to make sure this
works.

> The documentation a bit partial concerning the intuition behind the lock
> modes,
> and examples of using them (beyond the Star War example).

I think the best way to understand the documentation is to note which
locks conflict and which statements acquire them. That way if you
want to block an operation you can figure out which locks block that
operation and choose the one that has the fewest side effects.

> 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).

No. T2 sees the row in the state before T1 touched it. Only if T2 also needs
to update that row, does it need to wait.