Task
A user can only collect ONE bonus ONCE on the website per day.
Server structure:
These two web servers are deployed with the same code and are connected to the same database.
Requests are randomly directed to one of web servers.
What I have
I wrote the whole business logic, check if this is user's first request of the day, whether this user is legit etc, in one procedure.
I use @Transactional in Spring Framework in the hope of making the procedure I wrote transactional in the DB.
Problem
A user sent 10,000 requests at the same time and two of them were directed to two different servers and invoke the procedure run simultaneously, which means the user received TWO bonuses this day.
Help
So, from what I understand, @Transactional in Spring Framework blocks the code's access to DB, but not the DB directly? Users from Server A can still have access to tables in DB when Server B is running methods annotated with @Transactional?
And, how can I use transaction in a stored procedure in Oracle?
Thanks in advance.
Each connection to Oracle is a transaction; it's implicit in Oracle unlike SQL Server where you need to specify begin trans. If you have a web farm / garden directly connecting to the databse and a load balancing switch sends request A and then B to web server 1 and 2 you are going to get two transactions against the database. By default Oracle's isolation level is set to read committed. If you have something in your stored proc querying to check if the bonus has been applied you would want to select for update to lock the row so the other transaction would be blocked from reading the row until the other transaction finishes the update. Also, have you considered using any sessions to keep each session sticky to one web server? Otherwise I would consider using some middle ware code following the CQRS pattern as another alternative prior to the requests getting sent to the database: http://blog.trifork.com/2010/01/27/cqrs-designing-domain-events/