锁表火鸟，德尔福 [英] Locking tables firebird, delphi

问题描述

我有两个应用程序。一个是从表一到另一个按需同步存在的主要应用（不要问我为什么）。其他应用程序将数据从同步表填充到主表。

当其他应用程序正在运行并填充表时，我需要在主应用程序中知道该进程正在运行，并禁止按需同步在主要应用中。我正在考虑在一个交易中制作一些表并将其锁定在同步中。当填写数据完成时释放锁。

我对Delphi中如何处理方法感兴趣，如何设置事务同步和主应用程序？如何检查表是否锁定？目标也是如果同步应用程序停止释放锁定。

谢谢

解决方案

p> Firebird是版本控制引擎，锁定有自然的事情。引擎，库，他们都被优化，以避免锁定。我建议你几步回来，大量看照片。您可以根据应用程序行为更好地制定您的任务，然后考虑如何修改其对数据库的行为，以获得所需的内容。

并且pleease！给应用一些名字。 这个程序和其他应用程序这个问题很难考虑和计划 - 你不可避免地会混合它们。

在Firebird中，你可以锁定单行，以便锁定表，您应该创建一个由单行组成的表），但即使这种行为也是有问题的，只能由其他应用程序检查，试图更改相同的记录和提交更改。 OldSchool变体看起来像更新FlagTable SET FlagColumn = FlagColumn + 0 / * WHERE ID = ... * / 和现代版本引入 SELECT ... 。FOR UPDATE WITH LOCK statememt

• C：\Program Files\Firebird\Firebird_2_5\doc \sql.extensions\README.select_expressions.txt


• http：// www .ibase.ru / devinfo / pslock.htm - 通过AltaVista BabelFish或通过Google翻译阅读

然而让我强调它再次 - 甚至每行锁在Firebird是一个非常特殊的方式创建和检查的自然和异国情调。每张桌子排只能被滥用此功能，并锁定表中的每个最新行，这可能会使服务器跪下试图强制他进入模式，他被设计为不惜一切代价避免。

没有关于您的应用程序的更多详细信息，以及关于它们的权力以及数据流的细节，我们几乎不能提出最佳选择。不过我可以帮你探索几条路线。

_1。只需通过单个事务插入。

如果 insert-application 不会 commit transaction ，直到最后一个数据行被插入，然后同步读取应用程序将不会看到该数据。这是不通用的 - 因此看不见。

这是一种基于所有事务性质的最自然的方法真正的SQL服务器。它有一个缺点：如果插入过程很慢（例如，您只能计算一行每分钟，需要计算并插入100行），这将持有非常长的非提交事务，这将对垃圾收集造成不良影响和服务器性能。但是，这是最简单的方法来尝试看看。

_2。使用全局临时表。

• http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html


• 有没有办法在Firebird存储过程中创建本地表变量？


• http://fhasovic.blogspot.ru/2005/01/global-temporary-tables.html


• http://firebirdsql.su/doku.php?id=create_global_temporary_table

您的插入应用程序使用每个连接的GTT（必须：它应该是每个连接，交易！），并填写它，只要它分解长的进程到数字交易，如果它会发现这是有用的。它不会触摸主表，所以同步阅读应用程序可以保持同步尽可能多。

每次连接之后，GTT填满数据，一个导出事务在单次操作之后仓促提交： INSERT INTO MAIN-TABLE SELECT * FROM GTT 。

• 还有SQL MERGE命令执行INSERT-OR-UPDATE功能，如果这是真正需要的。

在这个单一操作之后，您提交 export 事务，并与数据库断开连接。此断开连接将以最有效的方式从每个连接GTT中刷新所有数据。如果需要继续工作，您可以重新连接一次。

如上所述，此策略似乎不会影响同步的工作流程，阅读应用程序，只需要从插入应用程序

_3中的良好实现。真正的锁定方法将需要一个复杂的解决方案，修改两个应用程序。

1. 您实现了列出<$ c $的特殊更新表c> connectionID （或几个）每个活动的插入应用程序


2. 您修改同步阅读应用程序所以它监听Firebird事件


3. 你修改同步阅读应用程序所以它具有内部的锁定模式，并自动避免读取数据库，除了罕见的保持活动请求，如 SELECT 1 FROM RDB $ DATABASE 每分钟一次。


4. 您修改同步阅读应用程序，因此连接后，它在 READ COMMITED mode并进入锁定模式，除非表为空


5. 您修改同步阅读应用程序，以便接收UPDATERS_CHANGED事件将立即进入锁定模式，然后等待几秒钟，然后阅读updaters 表，看看它是否为空，它可以退出锁定模式。


6. 您创建后插入或更新或删除触发器code> POST_EVENT UPDATERS_CHANGED事件


7. 您修改插入应用程序
code>，使其在连接后立即将其连接注册到updaters表中，并立即使用提交 - 参见 http://www.firebirdsql.org/refdocs/langrefupd15-current_connection.html


8. 你实现ON_DISCONNECT数据库触发器，以便如果当前断开连接的应用程序是插入应用程序之一，则从updaters表中删除这些行。

   
   
   
   
   
   • http://firebird.1100200.n4.nabble.com/Question-on-ON-CONNECT-ON-DISCONNECT-trigger-td1119134.html
   
   
   • http://www.firebirdsql.org/refdocs/langrefupd25-ddl-trigger。 html
   
   
   • 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
   
   

像

  DELETE FROM updaters你在哪里不存在（
 SELECT * FROM MON $ ATTACHMENTS M 
 WHERE M.MON $ ATTACHMENT_ID = u.CONNECTION_ID）
  

我想重复一下，该方案依赖于从数据库服务器和wilingly自愿接收EVENT的同步读取应用程序的行为，自动进入锁定模式。

而且，基于Firebird的基于版本的性质，这种方案几乎不需要，上面概述的更简单的无锁方法就足以满足大多数任务。

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

解决方案

Firebird is versioning engine and locking there is innatural 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 pleease! give applications some name. It is very hard to think and plan in terms "this app" and "other app" - yuyou 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 statememt

• 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 innatural and exotic condition with a very specific way to create and check for. Per-table row can only be achived abusing this feature and locking every sginle row in the table, which might bring the server to its knees trying to force him into the mode, that he was designedto 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 sugegst 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 uncommited - 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-commited 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.

• http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html
• 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-applicationutilizes a per-connection GTT (essential: it should be per-connection, not per-transaction!) and fills it as long as it please 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 strarted that is hastily commited after a single operation: INSERT INTO MAIN-TABLE SELECT * FROM GTT.

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

After this single operation you do commit the export transaction and do 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 would 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 receiving UPDATERS_CHANGED event it would enter "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 http://www.firebirdsql.org/refdocs/langrefupd15-current_connection.html

8. you implement ON_DISCONNECT database trigger so that if the currently disconnecting application was one of the inserting-applications ones - you remove those rows from the "updaters" table.

   • http://firebird.1100200.n4.nabble.com/Question-on-ON-CONNECT-ON-DISCONNECT-trigger-td1119134.html
   • http://www.firebirdsql.org/refdocs/langrefupd25-ddl-trigger.html
   • 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 wilingly 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.

这篇关于锁表火鸟，德尔福的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！