如何使用信号量锁定表？ [英] How to use semaphore to lock table?
问题描述
我想在事务执行时从读取锁定MDB表。我会使用 dbDenyRead 但显然这是不可靠的，并不总是锁定表：
I would like to lock an MDB table from reads while a transaction executes. I would use dbDenyRead but apparently this is unreliable and doesn't always the lock the table:
http://www.office-archive.com/32-ms-access/c2bd1a2553e79c60。 htm
如何使用信号量解决方案在表上实现虚拟锁定？
How can I use a semaphore solution to achieve a virtual lock on a table?
如果我将信号量存储在另一个表中，其中一行包含表名和一个进程/工作站ID，它将在事务结束时清除，那么如何防止以下序列？
If I store the semaphore in another table with a row holding the table name and a process/workstation ID which will be cleared at the end of the transaction, how can I prevent the following sequence?
- 处理更新具有处理A ID的信标表。
- 处理B更新处理B ID的信标表。
- 过程A和B都执行交易（而不是我想要的）。
<
- Process A queries semaphore table and finds it blank.
- Process B queries semaphore table and finds it blank.
- Process A updates semaphore table with Process A ID.
- Process B updates semaphore table with Process B ID.
- Both Process A and B perform transaction (not what I want).
没有答案包括使用 IF SQL语句，因为我的版本的JET-SQL不能使用。
Please no answers that include the use of the IF SQL statement because my version of JET-SQL can't use that.
推荐答案
我不知道这是否会处理你所有的问题。但创建一个主键不是自动生成的表。如果可能，还可以用某种检查/规则声明相同的列，并强制该列只包含一个可能的值。
I'm not sure if this will address all of your issues. But create a table with a Primary Key that isn't auto-generated. If possible, also declare the same column with some kind of check/rule that enforces that the column may only contain one possible value.
现在， 0或1行。要获得信号量，请在表中插入一行，并使用单个固定的PK值。如果此插入成功，您就拥有该信号量。如果由于主键冲突导致插入失败，那么您没有获取信号量。
You now have a table that can contain 0 or 1 rows. To obtain the "semaphore", insert a row into the table, with the single fixed PK value. If this insert succeeds, you own the semaphore. If the insert fails due to a primary key violation, then you didn't obtain the semaphore. The key is to not perform a preliminary check first - just attempt the insert.
如果你的技术是如此破碎，它不能保证
If your tech is so broken that it can't guarantee that the PK constraint will be maintained, then you seriously need to consider changing technology.
如果你失败插入行，那么你可以
If you fail to insert the row, then you can start regularly polling this table - at whatever intervals seem appropriate.
另一种方法是使表具有自动编号PK列 - 当您想要获取信号量时，在表中插入一行。然后，查询表以查找具有最低PK列值的行。如果该行是行，那么您现在拥有该信号量。您仍然必须轮询，但您有效地在此表中记录了一个保留。
The other alternative is to have the table have an autonumber PK column - when you want to obtain the semaphore, insert a row into the table. Then, query the table to locate the row with the lowest PK column value. If that row is your row, then you now own the semaphore. You still have to poll, but you effectively have a "reservation" recorded in this table.
与前一种情况一样，一旦您完成了信号量保护工作，从表中删除您的行。第二种方法应该是更公平（每个进程按照请求的严格顺序获得访问），但在实践中可能看起来更混乱。它依靠所有进程保持live。
As with the previous case, once you have completed your semaphore protected work, you delete your row from the table. This second approach should be "fairer" (in that each process gains access in strict order of requests), but may look be messier in practice. It does rely on all processes staying "live".
这篇关于如何使用信号量锁定表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
