Flyway locking fails for "single-writer" Galera clusters

Flyway's locking mechanism fails to prevent concurrent migrations on a "single-writer" Galera cluster (where all DB writes go to a single node). The issue occurs even on a cluster with a single node, as long as wsrep_on=ON.

This is related to a Flyway workaround shipped in 9.19.0 where the table locking mechanism was changed from GET_LOCK to SELECT * FROM <history> FOR UPDATE due to MariaDB throwing an error for GET_LOCK with wsrep_on=ON at that time (see #3675). MariaDB has since fixed the underlying issue in MDEV-31325 so that GET_LOCK no longer throws, and shipped the fix in 10.6.14 and 10.11.4 (about 3 years ago). The Flyway workaround, however, is still in place, and effectively makes it so that a much weaker locking mechanism is used.

On a "multi-writer" cluster, neither GET LOCK nor FOR UPDATE work properly, because both are node-local. On a "single-writer" cluster (which is the common case), GET_LOCK works correctly while FOR UPDATE doesn't, because:

• FOR UPDATE uses optimistic locking in Galera, so the lock isn't held immediately, but instead only manifests as an "optimistic locking"/"deadlock" exception once the transaction is committed
• FOR UPDATE is transaction-scoped and MySQL/MariaDB implicitly commit around DDL (which can end the transaction and release the lock mid-run)
• SELECT * FROM <history> FOR UPDATE is also effectively a no-op on an empty table

---

The proposal would be to either outright switch back to GET_LOCK, or at least make GET_LOCK an option again, e.g. by:

• adding a configuration property for it, or
• checking the MariaDB version and enabling GET_LOCK for versions that support it (>10.11)
• attempting a GET_LOCK, catching the potential SQL error (1235), and falling back to FOR UPDATE

This will make the table locking work again, at least for "single-writer" clusters.

(Update: I've now added PR #4277 that implements the configuration property approach)