I've been reading about Oracle data consistency guarantees, and supported transaction isolation levels, (e.g. here: https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT121) and I feel like I'm getting a lot of high-level information, but I'm not sure how it applies to my specific question.
I'm going to describe a simplified version of my use case, and I'm looking for convincing answers, preferably with references, as to how I need to structure my transaction to get the desired result. (Please don't get too hung up on syntax or data normalization or even data types in my question; it's a straw man -- so if you know what I mean, move on and focus on the concurrency problem.) :)
The Scenario (simplified):
Many users (tens of thousands) are playing an online game simultaneously. Players are all members of two teams, red or blue. Every time a player finishes a game, we want to log the user, their team affiliation, a timestamp, and the score. We also want to aggregate the highest score ever achieved by each team. Our data model looks like this:
// each game is logged in a table that looks kind of like this:
GAMES {
time NUMBER,
userid NUMBER,
team NUMBER,
score NUMBER
}
// high scores are tracked here, assume initial 0-score was inserted at time 0
HIGH_SCORES {
team NUMBER,
highscore NUMBER
}
So, for each score report I receive, I execute a transaction that looks like this
BEGIN
UPDATE HIGH_SCORES set highscore=:1 WHERE team=:2 and :1>highscore;
INSERT into GAMES (time, userid, team, score) VALUES (:1,:2,:3,:4);
COMMIT
The invariant I wish to preserve is that, at any point in time, the high score for each team, as shown in the HIGH_SCORES table will be the highest score I would find if I were to scan the GAMES table and find highscore the hard way.
My understanding of the READ_COMMITED isolation level suggests this won't get me what I want:
Conflicting Writes in Read Committed Transactions
In a read committed transaction, a conflicting write occurs when the transaction attempts to change a row updated by an uncommitted concurrent transaction, sometimes called a blocking transaction. The read committed transaction waits for the blocking transaction to end and release its row lock.
The options are as follows:
If the blocking transaction rolls back, then the waiting transaction proceeds to change the previously locked row as if the other transaction never existed.
If the blocking transaction commits and releases its locks, then the waiting transaction proceeds with its intended update to the newly changed row.
It seems to me that if red team (team 1) has a high score of 100, and two players simultaneously submit better scores, a multithreaded server could have two database transactions going on that begin at the same time:
# Transaction A
UPDATE HIGHSCORES set highscore=150 where team=1 and 150>highscore;
INSERT into GAMES (time, userid, team, score) VALUES (9999,100,1,150);
and
# Transaction B
UPDATE HIGHSCORES set highscore=125 where team=1 and 125>highscore;
INSERT into GAMES (time, userid, team, score) VALUES (9999,101,1,125);
So (In READ_COMMITED mode,) you could get the following sequence: (c.f. Table 9-2 in the Oracle link referenced above)
A updates highscore for red team row; oracle locks this row
B still sees the 100 score and so tries to update red team highscore;
oracle Blocks trasaction B because that row is now locked with a conflicting write
A inserts into the games table;
A commits;
B is unblocked, and completes the update, clobbering the 150 with a 125 and my invariant condition will be broken.
First question -- is this a correct understanding of READ_COMMITED?
My reading of SERIALIZABLE, however:
Oracle Database permits a serializable transaction to modify a row only if changes to the row made by other transactions were already committed when the serializable transaction began. The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began.
Suggest that serializable won't do the right thing in the above scenario either, the only difference being that transaction B would get an error and I would have options to rollback or try again. This is doable, but seems unnecessarily difficult.
Second question -- is this a correct understanding of SERIALIZABLE?
... And if so, I am flummoxed. This seems like a simple, common thing to want to do. In code, I could accomplish this trivially by putting a mutex around the test-and-update of each team's high score.
Third and most important question: How do I get Oracle (or any SQL database, for that matter) to what I want here?
UPDATE: further reading suggests I probably need to do some explicit table locking, as in (https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm) -- but I'm not clear on exactly what I need. HALP!?
