Firebird lock "table" to prevent inserts

Is possible to lock a table to prevent inserts/updates/deletes ?

I need to lock a table, delete some records and "reset" a generator.

>> lock table logs to prevent inserts/updates/deletes;
delete from logs where id <= :lastIdProcessed;
if ((select count(1) from logs) = 0) {
    alter sequence logSequence restart with 0;
}
>> commit and release table

Another question is, I'll get in throwble if there is an insert into logs (gen_id(logSequence,1)) waiting for the lock release, the insert will run with the logSequence incremented using its value before it restart?

Solution

Depending on your needs you can start a snapshot table stability transaction with table reservation using

SET TRANSACTION 
    READ WRITE 
    ISOLATION LEVEL SNAPSHOT TABLE STABILITY 
    WAIT
    RESERVING <tablename>, <tablename> FOR PROTECTED WRITE

SNAPSHOT TABLE STABILITY Isolation Level

The SNAPSHOT TABLE STABILITY isolation level is the most restrictive. As in SNAPSHOT, a transaction in SNAPSHOT TABLE STABILITY isolation sees only those changes that were committed before the current transaction was started. After a SNAPSHOT TABLE STABILITY is started, no other transactions can make any changes to any table in the database that has changes pending. Other transactions are able to read other data, but any attempt at inserting, updating or deleting by a parallel process will cause conflict exceptions.

The RESERVING clause can be used to allow other transactions to change data in some tables.

If any other transaction has an uncommitted change of data pending in any database table before a transaction with the SNAPSHOT TABLE STABILITY isolation level is started, trying to start a SNAPSHOT TABLE STABILITY transaction will result in an exception.

[..]

RESERVING

The RESERVING clause in the SET TRANSACTION statement reserves tables specified in the table list. Reserving a table prevents other transactions from making changes in them or even, with the inclusion of certain parameters, from reading data from them while this transaction is running.

A RESERVING clause can also be used to specify a list of tables that can be changed by other transactions, even if the transaction is started with the SNAPSHOT TABLE STABILITY isolation level.

One RESERVING clause is used to specify as many reserved tables as required.

See Transaction Statements for more information; you might also want to consult the old Interbase 6.0 documentation (especially Embedded SQL Guide and Developer's guide, see https://www.firebirdsql.org/en/reference-manuals/ at end) for information.

I have no experience using this feature, so I can't advise you on its potential pitfalls.