@mattab opened this Issue on July 3rd 2019 Member

Goal: minimise possible locks in Tracker to improve performance.

Concerns: what could be the worst side effects? inconsistent tracking data stored in some cases?

Note: this should ONLY apply in Tracker mode, not for API/UI/etc.

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_read-uncommitted

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

dirty read

  • An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.
  • This kind of operation does not adhere to the ACID principle of database design. It is considered very risky, because the data could be rolled back, or updated further before being committed; then, the transaction doing the dirty read would be using data that was never confirmed as accurate.
  • Its opposite is consistent read, where InnoDB ensures that a transaction does not read information updated by another transaction, even if the other transaction commits in the meantime.
  • See Also ACID, commit, consistent read, isolation level, READ UNCOMMITTED, rollback.
@tsteur commented on September 3rd 2019 Member

FYI: So far we have barely any deadlocks anymore. I reckon it might not really be needed anymore so much. Seems the changes we did helped avoid the deadlocks

@tsteur commented on August 2nd 2021 Member

For performance it might be actually quite good to change the isolation mode during tracking to read committed. This is also the default for most other RDBS and it should faster since less versions need to be maintained etc.

It be interesting to try this as it's quick to implement (we already have a way to set this for aurora using the aurora_readonly_read_committed ini setting) but the more difficult part is actually measuring it.

@tsteur commented on August 2nd 2021 Member

Overall it be also worth considering using read committed instead of repeatable read. Meaning for every query, whether it's archiving or something else.

@tsteur commented on January 13th 2022 Member

If someone has issues with gap locks, please comment and let us know about it.

Powered by GitHub Issue Mirror