0

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).

  • Are you sure that transaction B started after transaction A was committed? – ypercubeᵀᴹ Dec 20 '17 at 21:40
  • TransactionB may have started before TransactionA finished but probably got locked because of MasterRecordX lock. That may be why I don't see any writes from TransactionB until TransactionA finished. – rreyes1979 Dec 20 '17 at 21:54
  • If it started before tran A commit, then it is expected behaviour. The isolation level is REPEATABLE READ, not READ COMMITTED. – ypercubeᵀᴹ Dec 20 '17 at 21:55
  • If DetailRecordY was not read until TransactionA committed, then TransactionB should have seen the changes made by TransactionA. Or at least, that is what I am seeing from my tests. – rreyes1979 Dec 20 '17 at 21:58
  • 1
    It seems that most important information is missed - as it turned out later it was another query which is not mentioned in the question... – a1ex07 Dec 21 '17 at 16:54
2

The problem was related to the fact that there was a query being executed before MasterRecordX got locked. Here is what happened:

ConnectionA starts TransactionA
RecordW is queried.
MasterRecordX gets locked for update.
...
        ConnectionB starts TransactionB
        RecordW is queried.
        MasterRecordX gets locked for update.
        ...
DetailRecordY is queried
DetailRecordY is changed
...
ConnectionA commits TransactionA
        DetailRecordY is queried
        DetailRecordY is changed
        ...
        ConnectionB commits TransactionB

Because RecordW on TransactionB was queried before DetailRecordY was changed and commited in TransactionA, the changes were not seen by TransactionB. Because of the isolation level (REPEATABLE READ), once a query is executed within a transaction, no later data changes are seen within that transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.