Subject Re: [Firebird-general] Suggestion for enhancement
Author Ann W. Harrison
At 10:12 AM 7/29/2004, Daniel Rail wrote:

>But, allowing to set NOT NULL does require more work ...
>And, the most difficult
>part of the implementation would be that to set the NOT NULL flag of a
>field, it would probably require an exclusive access to the database
>or table

Actually, there's a mechanism for handling exactly that problem. A
transaction that has used a table gets a shared lock on the table's
existence. To change a constraint on a table (or a field within a table)
a transaction must get an exclusive lock on the existence of the table.
When it requests the exclusive lock, all transactions that hold a
shared lock on the table existence get an asynchronous notification
that they should release their locks.

Each of the other transactions checks on the state of its use of the
table. If it has a running request that uses the table, it holds on
to its shared lock until the request completes. Otherwise, it marks
its idea of the transaction as needing to be updated and releases its
lock and queues a request for a new shared lock. Once all other
transactions have released their locks, the one that wanted to change
the table gets its exclusive lock, makes its changes, and reduces its
lock to shared. Other transactions get their shared locks, re-read
the table definition and off they go, obeying the new rules.