5

I'm not sure how to achieve consistent read across multiple SELECT queries.

I need to run several SELECT queries and to make sure that between them, no UPDATE, DELETE or CREATE has altered the overall consistency. The best case for me would be something non blocking of course.

I'm using MySQL 5.6 with InnoDB and default REPEATABLE READ isolation level.

The problem is when I'm using RDS DataService beginTransaction with several executeStatement (with the provided transactionId). I'm NOT getting the full result at the end when calling commitTransaction.

The commitTransaction only provides me with a { transactionStatus: 'Transaction Committed' }..

I don't understand, isn't the commit transaction fonction supposed to give me the whole (of my many SELECT) dataset result?

Instead, even with a transactionId, each executeStatement is returning me individual result... This behaviour is obviously NOT consistent..

Yves M.
  • 26,153
  • 20
  • 93
  • 125

1 Answers1

1

With SELECTs in one transaction with REPEATABLE READ you should see same data and don't see any changes made by other transactions. Yes, data can be modified by other transactions, but while in a transaction you operate on a view and can't see the changes. So it is consistent.

To make sure that no data is actually changed between selects the only way is to lock tables / rows, i.e. with SELECT FOR UPDATE - but it should not be the case. Transactions should be short / fast and locking tables / preventing updates while some long-running chain of selects runs is obviously not an option.

Issued queries against the database run at the time they are issued. The result of queries will stay uncommitted until commit. Query may be blocked if it targets resource another transaction has acquired lock for. Query may fail if another transaction modified resource resulting in conflict.

Transaction isolation affects how effects of this and other transactions happening at the same moment should be handled. Wikipedia

With isolation level REPEATABLE READ (which btw Aurora Replicas for Aurora MySQL always use for operations on InnoDB tables) you operate on read view of database and see only data committed before BEGIN of transaction.

This means that SELECTs in one transaction will see the same data, even if changes were made by other transactions.

By comparison, with transaction isolation level READ COMMITTED subsequent selects in one transaction may see different data - that was committed in between them by other transactions.

Oleg Russkin
  • 3,476
  • 4
  • 18
  • You mean that the action of beginning a transaction "locks" (or snapshot) the database state. Any SELECT within the transaction will query the "transaction state" and not the actual database state.. Does that make sense? – Yves M. Dec 16 '19 at 13:08
  • Databases operate in highly concurrent environment, there can be thousands simultaneous transactions. Isolation level [controls](https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15) level of locking while reading data, how changes in one transaction should affect other transactions referencing this data, etc. BEGIN of transaction indicates point to which database can safely rollback in case.What data can be seen / changed depends on this transaction level, actions, changes, locks made by other transactions (committed and uncommitted). – Oleg Russkin Dec 16 '19 at 13:19
  • Thanks Oleg... so my question has nothing to do with AWS RDS.. it's only about my own miscomprehension of transactions.. Thank you very much for that explanation – Yves M. Dec 16 '19 at 13:31
  • In my case I'm doing no modifications at all.. only SELECT, that's why I'm confused – Yves M. Dec 16 '19 at 13:31
  • 1
    With REPEATABLE READ yes, one can see the state of db (committed) at the point of begin. Selects in one transaction will see same data. Subsequent selects in another transaction may see new data. With other isolation levels one may see committed changes made by other transaction, or even uncommitted changes. – Oleg Russkin Dec 16 '19 at 13:36
  • The only problem with REPEATABLE READ is [phantom reads](https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads) I must be aware of that – Yves M. Dec 16 '19 at 13:40
  • 1
    It might also depend on db engine implementation. MySQL - [By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html), [PostgreSQL](https://www.postgresql.org/docs/12/transaction-iso.html) – Oleg Russkin Dec 16 '19 at 13:49