Search Postgresql Archives

Locking & concurrency - best practices

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?










---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux