- 3,727,076 Users
- 2,245,317 Discussions
- 7,852,569 Comments
- 17.8K All Categories
- Industry Applications
- 3.2K Intelligent Advisor
- 1.6K On-Premises Infrastructure
- 579 Analytics Software
- 49 Application Development Software
- 1.8K Cloud Platform
- 700.5K Database Software
- 17.5K Enterprise Manager
- 20 Hardware
- 270 Infrastructure Software
- 141 Integration
- 74 Security Software
HotSpot record update problem
We have a database performance problem in our CSD (Central Securities Depository) software. In CSDs by nature, the sum of sold Securities could not exceed from the base balance. This rule is implemented in our CSD as follows (simplified) ... at first, one record is inserted into ACCOUNT table that represents the released Securities's base balance. Then for each sale, some records will be inserted into TRANSACTION and some other tables and then the result of subtraction of the current sale transaction amount from the Securities account balance will be updated as the Securities's new account balance. The sale process could not be done if the Securities balance is negative.
This implementation works fine when the number of sale transactions are small but for the big number of transactions, say for a 1B$ Securities that will be sold via ~100,000 transactions in one day, this method won't works and we get a lot of OptimisticLockException! To be more clear, we open <= 1200 connections from database and have <= 2000 threads in our java code and for the sake of historical (legacy code) reasons we use Hibernate! We did do a lot of optimizations in the code from using cache in different places to as miniaturizing the transaction as possible but still have performance problem. In addition to performance problem, this method causes a UX (User Experience) problem in which in most cases users must click on the sale button several times until they get the success message!!
This is obvious that the Securities base account is the bottleneck and updating its value via hundreds threads causes the problem. We thought several method to solve/alleviate this problem as listed below:
- Using two transactions instead of one for each sale
- Using a token pool instead updating Securities base account
- Serializing the updates on Securities base account
- and etc.
but all impose additional problems.
As I thought we aren't the first on the earth that experienced this problem, I decided to ask here if someone else had this problem and how [s|]he solved it? Or maybe there would be some Oracle's built-in stuffs that can helps!
p.s. we use oracle 12g