如何使用事务数据库创建关键部分？

发布于 2024-11-18 06:14:55 字数 544 浏览 1 评论 0原文

假设我有 2 台服务器与同一个事务数据库通信。

我想对其进行设置，以便这两台服务器中只有一台执行给定的定时操作（本质上使用数据库来强制同步）。据我所知，类似的事情可能会起作用：

假设我的表 TABLE 有 2 列，ID 和 STATUS。如果我这样设置代码：

update TABLE set STATUS = 'processing' where ID = 1234 and STATUS != 'processing'

if (weHaveModifiedAtLeastOneRow)
{
    // do critical section stuff here
    // This is code that we only want one server to run, not both.

    update TABLE set STATUS = 'free' where ID = 1234
}
else
{
    // We failed to get the lock, so do nothing
}

这会起作用吗？还是我在这里遗漏了一些概念？

原文

Say I have 2 servers that talk to the same transactional database.

I want to set it up such that only one of these two servers will perform a given timed action (essentially using the database to enforce synchronization). From what I've heard, something along the lines of this might work:

Say my table TABLE has 2 columns, ID and STATUS. If I set up the code as such:

update TABLE set STATUS = 'processing' where ID = 1234 and STATUS != 'processing'

if (weHaveModifiedAtLeastOneRow)
{
    // do critical section stuff here
    // This is code that we only want one server to run, not both.

    update TABLE set STATUS = 'free' where ID = 1234
}
else
{
    // We failed to get the lock, so do nothing
}

Will this work, or am I missing some concepts here?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我爱人 2024-11-25 06:14:55

如果您想要关键部分，请使用 dbms_lock.request 。您可以通过像这样分配唯一的方式获得有意义的锁句柄：

DBMS_LOCK.ALLOCATE_UNIQUE ( lockname =>  'MYAPP_' || ID, lockhandle => handle);
success := DBMS_LOCK.REQUEST(lockhandle => handle, timeout => 1);
if (success = 0) then
-- Do stuff
DBMS_LOCK.release(handle);
else
-- we waited a second and didn't got the lock.
end if;

如果您愿意，您可以在 id 上应用哈希和模运算符，将 id 值空间投影到已知数量的锁上，接受（低）风险拖延无关交易。

If you want a critical section, use dbms_lock.request. You could get a meaningful lock handle trough allocate unique like this:

DBMS_LOCK.ALLOCATE_UNIQUE ( lockname =>  'MYAPP_' || ID, lockhandle => handle);
success := DBMS_LOCK.REQUEST(lockhandle => handle, timeout => 1);
if (success = 0) then
-- Do stuff
DBMS_LOCK.release(handle);
else
-- we waited a second and didn't got the lock.
end if;

if you prefer it, you could apply a hash and the modulo operator over id, to project the id value space over a known number of locks, accepting a (low)risk of delaying unrelated transactions.

回复收藏 0 原文

~没有更多了~