数据库记录锁定 [英] database record locking

问题描述

我有一个服务器应用程序和一个数据库.服务器的多个实例可以同时运行，但是所有数据都来自同一数据库(在某些服务器上是postgresql，在其他情况下是ms sql server).

I have a server application, and a database. Multiple instances of the server can run at the same time, but all data comes from the same database (on some servers it is postgresql, in other cases ms sql server).

在我的应用程序中，执行了一个过程，该过程可能需要几个小时.我需要确保此过程一次只能执行一次.如果一个服务器正在处理，则第一个服务器实例完成之前，其他服务器实例将无法处理.

In my application, there is a process that is performed which can take hours. I need to ensure that this process is only executed one at a time. If one server is processing, no other server instance can process until the first one has completed.

该过程取决于一个表(我们称其为"ProcessTable").我要做的是，在任何服务器开始一个小时的处理之前，我在ProcessTable中设置了一个布尔标志，指示该记录已被锁定"并正在处理(并非该表中的所有记录都已被处理/锁定，所以我需要专门标记该过程所需的每条记录).因此，当下一个服务器实例出现而前一个实例仍在处理时，它将看到布尔标志并引发异常.

The process depends on one table (let's call it 'ProcessTable'). What I do is, before any server starts the hour-long process, I set a boolean flag in the ProcessTable which indicates that this record is 'locked' and is being processed (not all records in this table are processed / locked, so I need to specifically mark each record which is needed by the process). So when the next server instance comes along while the previous instance is still processing, it sees the boolean flags and throws an exception.

问题是，两个服务器实例可能都几乎同时被激活，并且当都检查ProcessTable时，可能没有设置任何标志，但是两个服务器实际上都在设置"标志的过程中但是由于事务尚未提交给任何一个进程，因此两个进程都不会看到另一个进程已完成锁定.这是因为锁定机制本身可能需要几秒钟的时间，所以存在机会窗口，其中2台服务器仍可以同时处理.

The problem is, that 2 server instances might both be activated at nearly the same time, and when both check the ProcessTable, there may not be any flags set, but both servers are actually in the process of 'setting' the flags but since the transaction hasn't yet commited for either process, neither process will see the locking done by the other process. This is because the locking mechanism itself may take a few seconds, so there is that window of opportunity where 2 servers might still be able to process at the same time.

看来，我需要的是设置"表中的一条记录，该记录应存储一个称为"LockInProgress"的布尔标志.因此，在服务器甚至无法锁定ProcessTable中所需的记录之前，它首先必须通过检查设置"表中的"LockInProgress"列来确保它具有执行锁定的完整权限.

It appears that what I need is a single record in my 'Settings' table which should store a boolean flag called 'LockInProgress'. So before even a server can lock the needed records in the ProcessTable, it first must make sure that it has full rights to do the locking by checking the 'LockInProgress' column in the Settings table.

所以我的问题是，如何防止两个服务器同时修改设置表中的LockInProgress列...还是我会以错误的方式进行操作?

So my question is, how do I prevent two servers from both modifying that LockInProgress column in the settings table, at the same time... or am I going about this in the wrong manner?

请注意，由于某些服务器使用一个数据库，而某些服务器使用另一个数据库，因此我需要同时支持postgresql和ms sql服务器.

Please note that I need to support both postgresql and ms sql server as some servers use one database, and some servers use the other.

预先感谢...

推荐答案

如何首先获取记录的锁定，然后更新记录以显示已锁定".这样可以避免第二个实例成功获得锁，从而导致记录更新失败.

How about obtaining a lock on the record first and then update the record to show "locked". This would avoid the 2nd instance to get a lock successfully and thereby the update of record fails.

关键是要确保锁定和更新是一个原子步骤.

The point is to make sure the lock and update as one atomic step.

这篇关于数据库记录锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！