Re: Locking & concurrency - best practices

Adam Rich wrote:
> I have a "parent_tbl" and dozens of data tables, with foreign keys
> referencing the PK of "parent_tbl" (one-to-many).  There are 100+
> users accessing the application, usually (but not always) each user
> is working on a different record in parent_tbl.  (this would seem like a pretty standard scenario for a lot of apps)
>
> Each user performs multiple queries in a transaction, reading and
> modifying the data in parent_tbl and multipe data tables before
> commiting.  I need the data to be consistent during and after the
> transaction.  (I basically need a way to lock a row in parent_tbl,
> and all rows in the data tables referencing that row, and prevent
> new rows from being inserted that reference that row).
>
> To guard against this, I added "FOR UPDATE" to queries against the
> parent_tbl and "LOCK TABLE IN EXCLUSIVE MODE" before queries against
> all of the data tables.  This works, except it slows down the entire
> application because all transactions are serialized.  Even users who
> are working on seperate records in parent_tbl are not allowed to
> proceed simultaneously.  This is not ideal, the vast majority of
> access to this database is users working on separate records.
>
> Should I drop the "LOCK TABLE" statements completely?  As long as
> *every* part of the application that modifies data obtains a
> "FOR UPDATE" lock on the parent table's record first, there shouldn't
> be any concurrency issues.  But, I realize I'm really only implementing
> advisory locking, and there's nothing preventing data corruption from
> any application that forgets or leaves out the "FOR UPDATE".
>
> Is this the best practice for dealing with this situation?  Should I
> be using real advisory locks instead of "FOR UPDATE" ?  What are the
> pros & cons of each?
>

In our program we wrote the locking into the program, and created a
modulelock table like:

create table moduelock(
   userid int,
   module int,
   primary key (userid, module)
)

The program then locks things before it uses them... but we also have
pretty low contention for modules.

A lock is:
begin
insert into modulelock...
commit;

if commit ok, then go ahead.  When we are done, delete from modulelock
where ...

-Andy