Locking tables firebird, delphi

Question

I have two applications. One is main application where exists on demand synchronization from tables one table to another(don't ask me why). Other application fills data from sync table to main table.

When other application is running and filling tables I need to know in main application that process is running and forbid sync on demand in main application. I was thinking about making some table and lock it with sync in one transaction. When finish with filling data release the lock.

I'm interested in the way how to do it in delphi, how to set transaction in sync and in main application? How to check if table is locked? Goal is also if sync application stops to release lock.

Thanks

Answer 1

Firebird is versioning engine and locking there is an unnatural thing. The engine, the libraries - they all were optimized to avoid locking. I suggest you take few steps back and looking at the picture in a large scale. You better formulate your task in term of applications behavior and then think how to modify their behavior towards the database to get what you need.

And please! give applications some name. It is very hard to think and plan in terms "this app" and "other app" - you just inevitably start mixing them.

In Firebird, you can lock the single row so to lock the table you should make a table consisting of the single row, but even this behavior would be problematic and can only be checked by other application trying to change the same record and commit the change. OldSchool variant is looking like UPDATE FlagTable SET FlagColumn = FlagColumn + 0 /* WHERE ID = ... */ and modern versions introduced SELECT .... FOR UPDATE WITH LOCK statement

• C:\Program Files\Firebird\Firebird_2_5\doc\sql.extensions\README.select_expressions.txt
• http://www.ibase.ru/devinfo/pslock.htm - read via AltaVista BabelFish or via Google Translate

However let me stress it again - even per-row locking in Firebird is an unnatural and exotic condition with a very specific way to create and check for. Per-table row can only be achieved by abusing this feature and locking every single row in the table, which might bring the server to its knees trying to force it into the mode, that it was designed to avoid at all costs.

Without more details about your applications and about your power over them and about the dataflow we can hardly suggest something optimal. However, I can suggest you to explore few routes.

_1. Just insert via single transaction.

If the inserting-application would not commit transaction until the last data row is inserted, then the syncing-reading-application just would not see that data. It is uncommitted - thus invisible.

That is a most natural approach based on the transaction-based nature of all real SQL servers. It has a drawback though: if insert process is slow (for example you can only calculate one row per minute and need to calculate and insert 100 rows) that would hold a very long non-committed transaction, which would cause bad influence on garbage collection and server performance. But that is the most simple way to try and see.

_2. Using Global Temporary Tables.

• TABLE in Firebird 2.5 Language Reference
• Is there any way to create a local table variable inside a Firebird stored proc?
• http://fhasovic.blogspot.ru/2005/01/global-temporary-tables.html
• http://firebirdsql.su/doku.php?id=create_global_temporary_table

Your inserting-application utilizes a per-connection GTT (essential: it should be per-connection, not per-transaction!) and fills it as long as it pleases, splitting long process to numeric transactions if it would find this useful. It does not touch the main table so the syncing-reading-application can keep syncing as much as it please.

After per-connection GTT is filled with data, the one "export" transaction is started that is hastily committed after a single operation: INSERT INTO MAIN-TABLE SELECT * FROM GTT.

• there is also SQL MERGE command that implements INSERT-OR-UPDATE functionality if that is what you really need.

After this single operation, you commit the export transaction and disconnect from the database. This disconnect would flush all data from the per-connection GTT in a most efficient way. You can re-connect a second later if you need to continue work.

Just like above, this strategy seems not affect the workflow for syncing-reading-application and only need good implementation from the inserting-application.

_3. The really locking approach would require a complex solution, modifying both applications.

1. you implement the special "updaters" table that lists connectionID (or few ones) for every active inserting-application.
2. you modify syncing-reading-application so it listens for Firebird Events
3. you modify syncing-reading-application so it has internal "locked" mode and voluntarily avoids reading the database except for rare keep-alive requests like SELECT 1 FROM RDB$DATABASE once per minute.
4. you modify syncing-reading-application so after connection it reads "updaters" table in READ COMMITED mode and enters "locked" mode unless the table is empty
5. you modify syncing-reading-application so that on receiving UPDATERS_CHANGED event, it enters "locked" mode immediately, then wait few seconds, and then read "updaters" table to see if it is empty and it can exit "locked" mode.
6. you create AFTER INSERT OR UPDATE OR DELETE trigger over "updaters" table so it would POST_EVENT the UPDATERS_CHANGED event
7. you modify the inserting-application so that it registers its connection into "updaters" table right after the connection and commits it immediately - see CURRENT_CONNECTION.
8. you implement an ON DISCONNECT database trigger so that if the currently disconnecting application was one of the __inserting-application--s ones - you remove those rows from the "updaters" table.

• http://firebird.1100200.n4.nabble.com/Question-on-ON-CONNECT-ON-DISCONNECT-trigger-td1119134.html
• TRIGGER in the Firebird 2.5 Language Reference
• http://ftp.uni-erlangen.de/pub/firebird/doc/README.monitoring_tables.txt
• http://ibexpert.net/ibe/index.php?n=Doc.SystemObjects
• http://www.upscene.com/documentation/fbtm2/index.html?dm_monitoringtables.htm

Something like

DELETE FROM updaters u WHERE NOT EXISTS ( 
   SELECT * FROM MON$ATTACHMENTS M 
   WHERE M.MON$ATTACHMENT_ID = u.CONNECTION_ID  )

I want to repeat that this scheme relies on the behavior of syncing-reading-application that willingly receives EVENT from database server and willingly and voluntarily enters the "locked" mode.

And that this scheme is hardly needed given version-based nature of Firebird and more simpler lock-less approaches outlined above should suffice for most of tasks.

Answer 2

Use proper firebird transaction mode (SNAPSHOT NOWAIT) or (SERIALIZABLE)