On 3/11/06, Bill Moseley <moseley@hank.org> wrote:
> 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.
serializable transactions ensure that any data read in-transaction
(but not locked) stays consistent for the remainder of the
transaction. It prevents the following in pseudo sql:
begin;
select into account_balance balance from account where account_id = xxx;
[do some stuff that generates n from account_balance]
update account set balance = balance + n where account_id = xxx;
commit;
if you are not using serializable transactions, there is a race on
balance getting updated because it isn't locked on the select. Now,
you could lock it on the select by adding 'for update' which would
more or less eliminate the need to serialize *if all the transactions
that modify balance follow this access pattern*.
now, on to your problem.
> 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"?
yes. IIRC this allows non locking readers to read the table but
serializes locking writers which is exactly what you want. Note that
this means that two reservations cannot occur at the same time. But
since you defined the problem that one reservation may affect how the
next one is granted, this is pretty much the only way unless you get
into a lazy evaluation of reservation state.
merlin