High-concurrency response for processing data in Oracle databases

Some systems may have high requirements on the system's response to high data concurrency (for example, the flight ticket booking system). This article uses Oracle's lock table to solve this problem:

The key code in Procedure is as follows:

---------------------------------------

Lock table tb_book_ticket in share mode;

...

Insert into tb_book_ticket (id, scheduled_flight, ticket_no, book_time) values (ID. nextval, v_scheduled_flight, v_ticket_no, SYSDATE); -- mark1

... -- Mark2

Select count (*) into v_book_count from tb_book_ticket where scheduled_flight = v_scheduled_flight; -- mark3

If v_book_count <= v_book_max_count then

V_result: = book successfully;

Commit;

Else

V_result: = book unsuccessfully;

Rollback;

End if;

Return v_result;

-------------------------------------------------------------

Note the following two points:

1) Use the lock table table_name in share mode instead of the lock table table_name in exclusive mode to improve concurrency and minimize unnecessary execution waits.

2) According to the general idea, mark1 and mark3 may be exchanged in sequence, but this is not feasible.

-------------------------------------------------------------

Select count (*) into v_book_count from tb_book_ticket where scheduled_flight = v_scheduled_flight; -- mark3

... -- Mark2

If v_book_count <v_book_max_count then

Insert into tb_book_ticket (id, scheduled_flight, ticket_no, book_time) values (ID. nextval, v_scheduled_flight, v_ticket_no, SYSDATE); -- mark1

-- Mark4

Commit;

End if;

-------------------------------------------------------------

Imagine if userA is executed to mark4 and userB is executed to mark3 at the same time, there may be situations where userB has booked a ticket but actually has no seats because userA has not been commit.

If you really want to use the regular logic to implement it, you need to use the read lock function of the Oracle table (fine-grained access policy), which is beyond the scope of this article and is invalid for SYS users.