critical sections

[humanista (mruczus)]

One table in my interbase 6 open source database must not exceed
recordcount= 10, so I thought I could do

select count(*) from MyTable;
if count(*)<10
insert into MyTable

but another copy of my application could do the same between my "select"
and "insert" and so both copies would do an insert that way possibly
exceeding recordcount=10. Should I use a critical section inside my
transaction?

[Bill Todd [TeamB]]

Use a before insert trigger that raises an exception if the insert
would result in more than ten records in the table.

[Craig Stuntz [TeamB]]

Post by Bill Todd [TeamB]
Use a before insert trigger that raises an exception if the insert
would result in more than ten records in the table.

Even this method would not necessarily catch multiple, concurrent
transactions, as a transaction can only count records visible to it and
will never see uncommitted records. I think that the only way you can
do this and be absolutely certain that you'll never exceed 10 records
is to pre-fill the table and not allow inserts or deletes. Just update
a record to market as "in use" or "not in use."