Is it possible for 2 non concurrent connections to see the same data for a record even as the 1st connection commits its changes?
Here is an example of what I am seeing from the audit logs:
ConnectionA starts TransactionA MasterRecordX gets locked for update. ... DetailRecordY is queried DetailRecordY is changed ... ConnectionA commits TransactionA --- ConnectionB starts TransactionB MasterRecordX gets locked for update. ... DetailRecordY is queried DetailRecordY is changed ... ConnectionB commits TransactionB
The problem is that when DetailRecordY is queried on TransactionB, it does not see the changes made by TransactionA even though MasterRecordX is locked at the begining of both transactions.
The audit logs also show that the 2 requests were non concurrent (around a second between one and the other).
Also, DetailRecordY is not queried before MasteRecordX gets locked. The database is mySQL and the isolation level is the default (REPETABLE READ).