Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
Description
Background: Innodb could acquire S and GAP locks other than what is required for isolation, typically during unique secondary index insert and purge. It is also possible to acquire X locks for records that are not modified finally. Such locks could impact replica while trying to replay independent transactions in parallel. Specifically for XA transaction replayed up to XA PREPARE stage.
Following projects were done to address the issue by releasing some locks early at XA Prepare.
MDEV-26682 slave lock timeout with xa and gap locks: 10.5.13
MDEV-33454 release row locks for non-modified rows at XA PREPARE: 10.6.18
Theory: It is not possible to do any read or write by a transaction after XA prepare and it doesn't break 2PL and it doesn't break conflict serializability in theory. The locks that we must hold are the X locks taken for which actual modification has taken place. The locks that are possible to release are the S locks, GAP locks and X locks for which we haven't actually made any modifications. The last case could happen, for example, for update and delete when some condition in SQL mismatches after the records are scanned and locked in Innodb. One fine point to note here is that when we release such locks at XA PREPARE in primary(master) the serializability order shifts from commit to prepare for XA transactions and for statement based replication to work the transactions must be replayed in replica strictly maintaining this order.
Once we ensure functional correctness, we need to evaluate the performance impact. Releasing S(and GAP) locks are straight forward while releasing X locks for unmodified records could be very expensive. More so for secondary index when there are other concurrent operations mainly because we don't have trx_id mark for modification of secondary index records.
Another point to note here is the extra X locks are acquired during scan phase and is generally not required in replica for row based replication where we already have pre-identified record to be modified. The only case where it helps is when a table ha no primary key and no unique secondary key. So the user case should be rather rare and we could actually be slowing down by trying to release those X locks.
Based on above analysis and discussion with marko we think it makes sense to allow user control this early lock release via a configuration variable.
Here is the tentative specification I propose.
1. We should attempt such early release of lock only in replica.
2. We should have a configuration to control the locks released.
3. By default release only shared locks (S and GAP)
Configuration: replica_early_lock_release
Type: Enum
Values: NONE, SHARED, EXCLUSIVE
Default: SHARED
Attachments
Issue Links
- is caused by
-
MDEV-33454 release row locks for non-modified rows at XA PREPARE
-
- Closed
-
- relates to
-
MDEV-26682 slave lock timeout with xa and gap locks
-
- Closed
-
-
-
- Closed
-
Activity
Hi serg,
Here is how the variable should be set.
1. The default value SHARED should be good for most of the users.
2. If user is having tables with no primary key and faces slow down in replica due to repeated deadlock and restart "EXCLUSIVE" could be attempted.
3. Setting "NONE" would disable this optimization. This is a safe option to have in case any issue is found related to early lock release.
May be marko, vlad.lesin, Elkin can elaborate more about [2] - the reason why X lock release was introduced in first place.
I don’t know exactly why MDEV-26682 and MDEV-33454 were deemed necessary to implement. The description of MDEV-26682 provides some reasoning but forgets to mention that the primary server and a replica may also choose to use entirely different index access paths. (InnoDB does not implement table row locks; it implements record locks in each index.) The width of gap locks may also depend on when some committed history is purged, because a gap lock might be attached to a committed delete-marked record.
I am a bit worried about any early lock release. It would seem to open some ways to break transaction isolation. We have had large deficiencies in this area, until the innodb_snapshot_isolation option was introduced and finally enabled by default (MDEV-35124). I think that it would be good to apply https://mariadb.com/resources/blog/isolation-level-violation-testing-and-debugging-in-mariadb/ to XA transactions.
MDEV-26682 and MDEV-33454 were implemented precisely because the primary server and a replica may also choose to use entirely different index access paths. If they'd be always taking exactly the same index access path, they'd be always taking exactly the same set of locks and they'd always have exactly the same behavior.
Early lock release was considered safe at that point. It absolutely shouldn't be done when it impacts correctness, but we failed to come up with a single example when it might do so.
When a user needs to use what value? What's the guidelines?