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?