gh-ost does not define a transaction isolation level when it sets up new connections in go/mysql/connection.go. This means that gh-ost "trusts" the safety of the default isolation level of the MySQL server

In most situations this is probably a safe assumption, but I believe this could bite us if the server default is READ_UNCOMMITTED. This isolation could cause the min/max queries for row copying to return un-comitted rows. These rows may never actually be committed (due to ROLLBACK) or committed later than when they're read 😱

Luckily, the transaction isolation can be defined at a per-connection level. Setting a transaction isolation at a per-connection level allows us to be certain we're using the right isolation while allowing users to default to more-relaxed isolations. I would like to take that approach

There are 2 x isolations I can see fitting for gh-ost:

1. REPEATABLE_READ - this is the default for MySQL and is probably the most commonly used. It's snapshot-level isolation and gap-locking may be more strict than gh-ost really needs, however. This isolation can cause more redo/undo logging as well, although it may be negligible in this use-case
2. READ_COMMITTED - this always returns what has been COMMIT-ed to disk (not a snapshot in time) and disables gap-locking. As gh-ost uses autocommit=true our transactions are a single operation only (BEGIN => a single-operation => COMMIT), so I believe there would be no major difference to "visibility" of rows returned in this use case. If we did several queries per transaction (we don't) there would be a major difference, however. So in practice I think this isolation would just cause less overhead to the server, not a change to what rows are visible. Please correct me if I'm wrong here!

REPEATABLE_READ is the safest bet, but I am curious if the community has feedback on the feasibility of READ_COMMITTED becoming the default isolation for gh-ost, and if there are any gotchas/benefits I missed above 🤔

More on the differences between these two isolations in this great Percona Blog 👍

cc @dm-2 / @rashiq / @shlomi-noach / @ajm188 for thoughts/validation here 🙇