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

FLUSH TABLES keeps tables locked after timeout

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.1.21, 10.1.34, 10.2, 10.3, 10.4
    • 10.4
    • Locking
    • for example Linux version 2.6.32-696.10.1.el6.x86_64 (mockbuild@c1bl.rdu2.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-18) (GCC) ) #1 SMP Tue Aug 22 18:51:35 UTC 2017

    Description

      Transactions started while the database is waiting to finish a "FLUSH TABLES WITH READ LOCK" are still blocked, when the FLUSH command hits a timeout. In our environment this happens as follows:

      Session 1:

      select * from a; # this is a very long running statement, 1800 seconds for example

      Session 2:

      SET lock_wait_timeout = 10;
      flush tables with read lock; # this blocks due to session 1

      Session 3:

      select * from a where a.x=1; # this is executed, while session 2 is waiting in the flush tables

      After 10 seconds the session 2 gets a timeout. We then close the connection. But now session 3 is still blocked. The list of running statements shows, that it is "waiting for table flush". Once session 1 returns, session 3 continues. This is the behavior we see with version 10.1.34.

      In our live environment we see a slightly different behavior of session 3: Instead of waiting for session 1 to finish, it continues after exactly 600 seconds (our slow query log is full with such statements started shortly after we started "flush tables with read lock"). In our live system we are using version 10.1.21.

      In the slow query log we see one more interesting aspect. The relevant line reads:
      # Query_time: 601.052932 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
      It claims that the lock time was 0, so what did the query do all those 600 seconds?

      While our production tables have lots of indices we were able to reproduce it with a newly created table:
      create table a select id from b
      So we just had a table with lots of integers without indices. Still the same behavior.

      Is there any way how we can tell session 3 to continue?

      Attachments

        Activity

          People

            monty Michael Widenius
            thomas.mischke Thomas Mischke
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.