proposals for LLL, part 2 (locking) - Mailing list pgsql-hackers

I. First, we need not in long-term page/row locking unlike
systems using locks for concurrency/consistency control.
All what we need we already have: xmax. When UPDATE/DELETE
will like to change row with valid xmax, they will check
is this transaction running or not. If it's running then
backend will wait for xmax commit/abort: we'll add
transaction pseudo-table (just some OID) and acquire
exclusive lock on this table for starting transaction
(XID will be placed in LOCKTAG->tupleId). Other backends
will acquire share lock on xmax XID in this table and
so will wait for xmax commit/abort (when exclusive lock
will be released).
   So, we need in short-term locks only when actually
read/write rows from/to shared buffers. Each heap Seq/Index
scan will allocate BLOCKSZ space on the start, heap
access methods will copy valid rows there and release
share page_or_row lock before return. After that scan
will continue execution of all its joins and subqueries.
   Joins and subqueries can take long time and holding
locks for long time seems very bad to me: this means
lock escalation and increases the likelihood of deadlocks.

II. While we need not in long-term page/row locks we have to
implement some long-term table locks - mostly to give users
additional abilities for concurrency control.

I learnt locking in Oracle and it seems quite appropriate for us.

Oracle _table_ locking modes (in short):

1. Row Share Table Locks - acquired by

   SELECT ... FOR UPDATE (we can just update xmax in selected tuples...)
   LOCK TABLE table IN ROW SHARE MODE;

   Conflicts with 5.
   (UPDATE/DELETE/SELECT_FOR_UPDATE will conflict on the same rows).

2. Row Exclusive Table Locks - acquired by

   UPDATE, INSERT, DELETE
   LOCK TABLE table IN ROW EXCLUSIVE MODE;

   Conflicts with 3, 4, 5.
   (UPDATE/DELETE/SELECT_FOR_UPDATE will conflict on the same rows).

3. Share Table Locks - acquired by

   LOCK TABLE table IN SHARE MODE;

   Conflicts with 2, 4, 5. This mode likes our current READ lock.

4. Share Row Exclusive Table Locks (the most cool mode name -:))
       - acquired by

   LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

   Conflicts with 2, 3, 4, 5. (Exclusive rights to change
   table but allows SELECT_FOR_UPDATE).

5. Exclusive Table Locks - acquired by

   LOCK TABLE table IN EXCLUSIVE MODE;

   Conflicts with 1, 2, 3, 4, 5. Like our WRITE lock but
   allows reading (ie SELECT, without FOR UPDATE).

That's all -:))
These are long-term locks acquired for the duration of transaction.

But I would like to add two internal lock modes due to VACUUM.

6. AccessShareLock     - acquired by each DML statement
                         (INSERT, UPDATE, DELETE, SELECT)
                         for the duration of statement.

7. AccessExclusiveLock - acquired by VACUUM

: we can't vacuum a relation scanned by some other backend...
(BTW, having these ones we get rid of pg_vlock file...)


And now yet two another locks for DDL statements
(DROP/ALTER) for tables and indices only.

8. ObjShareLock     - acquired by heap/index open for the duration
                      of transaction.

9. ObjExclusiveLock - acquired by DROP/ALTER.

: SELECT doesn't aquire any type of lock except of
  AccessSharedLock but this is short-term lock and would we
  like disallow DROP TABLE that was read by some running
  transaction ?

DDL locks don't conflict with DML locks 'of course.

And also -:)) one special lock:

10. ExtendLock - acquired when backend need to extend relation.

-----

Comments?

Vadim
P.S. Shouldn't LLL be renamed to Short Term Locking - STL? -:))