Handling the concurrent request while persisting in oracle database?

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?

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?

---

It will only over write if you allow it. You can try something like

UPDATE seatTable
SET seatTaken = true
WHERE .. find the seat, flight etc.. AND seatTaken = false

This will return 1 row updated the first time and 0 rows updated after that.

---

As you mention, transanction settings will help you achieving one operation. The best way to enforce this kind of restrictions it to ensure that your relational model is constrained not to accept the 2nd operation once the 1st one succeeds.

Instead of having to do an update on a row, say update .... seat = "taken", create a reservation table (customer, flight, seat) which has a constrain (column:seat = unique) (lookup ora docs to learn the syntax for that on table creation). That way your reservation process becomes an insert in the reservation table and you can rely on the RDBMS to enforce your relational constrains to keep your business model valid.

e.g. Let t1 be the earlier operation time, you'll have:

t1=> insert into reservations(customer1,flight-x,seat-y) // succeeds. Customer 1 reserved the seat-y
t2=> insert into reservations(customer2,flight-x,seat-y) // fails with RDBMS unique constrain violated.

The only way to reserve seat-y again is to first remove the previous reservation, which is probably what your business process wants to achieve.

---

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;

---

Aside from doing everything in a single UPDATE by carefully crafting WHERE clause, you can do this:

Transaction 1:

• SELECT ... FOR UPDATE exclusively locks the row for the duration of the transaction.
• Check if the returned status of the row is "booked" and exit (or retry another row) if it is.
• UPDATE the row and set its "status" to "booked" - it is guaranteed nobody else updated it in the meantime.
• Commit. This removes the exclusive lock.

Transaction 2:

• SELECT ... FOR UPDATE blocks until Transaction 1 finishes, then exclusively locks the row.
• The returned status of the row is "booked" (since Transaction 1 marked it that way), so exit (or possibly retry another row).