Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24818

Concurrent use of InnoDB table is impossible until the first transaction is finished

    XMLWordPrintable

    Details

      Description

      After the changes made in the scope of MDEV-515, this basic scenario will no longer work:

      --source include/have_innodb.inc
      create table t1 (pk int primary key) engine=InnoDB;
      start transaction;
      insert into t1 values (1);
      --connect (con1,localhost,root,,)
      insert into t1 values (2);
      

      The second INSERT, which in earlier versions is instant, will now hang until the first connection finishes the transaction (or will time out if it doesn't happen within the timeout interval).

      While the locking change was clearly intentional and explicitly mentioned at least in the commit comment to MDEV-515 patch, I still file it as a bug, because I think the negative effect of this change may by far exceed the improvements that MDEV-515 introduces.

      The scope of MDEV-515 is a performance improvement of a very big LOAD DATA or INSERT SELECT into an empty table – big enough to make this performance improvement significant from the practical perspective. While certainly important, it is still a rather specific use case, and the gain is limited to faster execution.

      On the other hand, "create/empty a table and start using it concurrently" scenario above is as basic as it gets; and the loss can be not only in performance (in case the first transaction is reasonably fast and other ones are just delayed), but in more extreme cases, when the first transaction remains open for a long time, it can render the application non-functional – everything else will be failing with timeouts.

      The problem can be happening anywhere within an application, both in business logic and deep in maintenance/service jobs.

      It will represent as seemingly sporadic not reproducible weird delays and timeouts and will be nearly impossible to investigate – and even if the cause happens to be revealed, changing this logic in all existing applications won't be realistic.

      I don't think that even a possibility to "opt-out" the new behavior would be sufficient, as users are generally not aware of details of the internal implementation of applications they use, as well as database admins if, for example, they maintain a shared hosting.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: