Hi, After learning more about locking in MariaDB, I find I have more questions then when I started. So here I go…
Why does Repeatable Read use X locks for update operations? A SELECT in T1 will never see the updated value from T2 anyway, so why worry about what that real value is on disk (i.e why can’t T1 update the same value, there will still be consistency within the transactions). The only situation I can see it being useful, is in SELECT with S or X lock (i.e FOR UPDATE or LOCK IN SHARE MODE). Is this why it’s needed?
Why does Repeatable Read use pessimistic locking (X record locks and S gap locks) when doing table scans for X lock operations ? (i.e UPDATE). AFAICT it does not need to do this to do away with Phantom reads, but uses MVCC instead.
If you have your indexes created differently, you could do away with the extra locks anyway, so only having that single lock is also legitimate. So why not behave like Read Commited and release the locks? You would also then have consistency as to how your application might behave regardless of how the query does the lookup.
If I have made any incorrect assumptions, please let me know.