I need to insert a row, but how that row is inserted depends on the
number of items existing in the table. I initially thought
SERIALIZABLE would help, but that only keeps me from seeing changes
until the commit in that session.
Am I correct that if I need to insert a row into a table that contains
column info based on the state of the table I need to lock the table
in "share row exclusive mode"?
In my case I have a table that holds registrations, and a
registration has a column "status" that can be "confirmed", "wait
list", or "cancel".
Any inserts should be "wait list" if the number of existing
"confirmed" is > $max_confirmed OR if any rows are marked "wait list".
Obviously, I don't want to let another insert happen in another
session between the select and insert.
So, in that case is "share row exclusive mode" the way to go?
I'm not that clear how locking and serializable work together:
The serializable isolation level would only be needed if I wanted to
see a frozen view of other selects (on other tables) during the
transaction. That is, the locked table can't have updates in other
sessions due to the lock so I'll see a frozen view of that table
regardless of serializable.
In other words, using serializable doesn't add anything if the table
is already locked in the transaction and all I'm looking at is that
one locked table.
Thanks,
--
Bill Moseley
moseley@hank.org