在oracle数据库中持久化的同时处理并发请求? [英] Handling the concurrent request while persisting in oracle database?

问题描述

我有这样一个场景，在一个航空公司网站上(使用 Java)，两个不同的客户同时发送两个请求来预订同一家航空公司的同一个座位
从纽约到芝加哥.我使用的是 oracle 数据库，并且隔离级别是读提交的.这里我的问题是 oracle 数据库是否提供有什么解决方案可以处理这种并发情况? 我知道当第一个事务 DML 语句被触发时，它会锁定受影响的行并将在事务完成时释放，即发出回滚或提交.但是一旦提交完成，第二个请求将尽快进行第一个完成并将覆盖第一个.所以它没有帮助?

I have this scenario ,on a airline website (using Java) two separate customers send two requests at same time to book a same seat in same airline
from New York to Chicago. I am using the oracle database and isolation level is read committed.My question here is that does oracle database provide any solution to deal with this kind of concurrent scenario? what I know is when first transaction DML statement is fired it will get a lock on affected rows and will release when transaction completes i.e on issuing rollback or commit.But as soon as commit is done and second request will proceed as soon as first is completed and will override the first one.So it does not help?

是的，在 Java 中，我可以将我的 db 类设为单例，并在进行更新的方法上使用同步关键字.但是想知道有没有无论如何，我们可以在数据库级别本身解决这种问题吗?可能作为可序列化的隔离级别可以提供帮助.但不确定?

Yes in Java I can deal with making my db class as singleton and using synchronized keyword on method which is doing update. But want to know is there anyway we can this kind of issue at database level itself?Probably isolation level as serializable can help. But not sure?

推荐答案

为了处理网站中的并发性，一种常见的做法是在每条记录上都有一列，让您可以检查它在获得后是否已更新.上次更新日期或连续版本号(由触发器自动递增).

To handle concurrency in a web site a common practice it to have a column on each record that allows you to check it has not been updated since you got it. Either last update date or a sequential version number (auto incremented by a trigger).

通常你会读取数据(加上并发列)

Typically you will read the data (plus the concurrency column)

SELECT seat,etc,version_no
FROM t1
WHERE column = a_value

然后当用户最终开始预订座位时，除非有更新，否则更新将起作用.

Then when the user eventually gets round to booking the seat the update will work unless there has been an update.

(每次更新后版本号或更新日期都会改变)

(the version number or update date will change after every update)

BEGIN
    UPDATE t1
    SET seatTaken = true
    WHERE seatid = .....
    AND version_no = p_version
    RETURNING version_no INTO p_version;
EXCEPTION WHEN NOT_FOUND THEN
    --Generate a custom exception 
    --concurrency viloation the record has been updated already
END;

自动更新版本号的触发器看起来有点像这样

the trigger to auto update the version number would look a little like this

CREATE OR REPLACE TRIGGER t1_version
AFTER INSERT OR UPDATE ON t1
FOR EACH ROW
BEGIN
    IF :new.version_no IS NULL THEN
       :new.version_no  := 0;
    ELSE
       :new.version_no  := :old.version_no  + 1;
    END IF;
END;

这篇关于在oracle数据库中持久化的同时处理并发请求?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！