maria-developers team mailing list archive

[Sergey Vojtovich <svoj@xxxxxxxxxxx>]

Hi Jan,

we're trying to adapt MySQL WL#6671 to MariaDB, which is expected to boost
concurrent single table workloads.

MySQL solution is fully based on MDL. It caused substential changes to MDL,
which would be nice to avoid. We'd like to understand if we can achieve the
same effect with InnoDB locking.

According to WL#6671 InnoDB relied on THR_LOCK in following cases:

1. "To isolate HANDLER READ statements from LOCK TABLES WRITE statements."

   It is likely not a big problem if we decide to go non-MDL way.

2. "To isolate LOCK TABLES statements that lock tables for write implicitly
   or for read, both explicitly or implicitly, from concurrent DML statements."

   That's something that we're looking alternatives for. See below.

3. "Due to coding mistake thr_lock.c lock was necessary to isolate ALTER TABLE
   IMPORT/DISCARD TABLESPACE under LOCK TABLES from concurrent I_S queries/
   open HANDLERs."

   I agree with this patch, it is merged and pending approval (MDEV-7894).

4. "To indicate that InnoDB tables don't support LOCK TABLES READ LOCAL by
   upgrading TL_READ lock requested by statement to TL_READ_NO_INSERT."

   It is likely not a big problem if we decide to go non-MDL way.


The question is if InnoDB can do the following with reasonable effort:
- LOCK TABLES ... READ - protect table data from concurrent writers
- LOCK TABLES ... WRITE - pretect table data from concurrent readers and writers
- honor SELECT HIGH_PRIORITY when there's pending LOCK TABLES ... WRITE
  (do we need this?)
- honor LOCK TABLES ... WRITE LOW_PRIORITY when there're concurrent readers
  (do we need this?)

Thanks,
Sergey