Query Q1 is executed which selects from table a.

a is locked for writing (L1) while the Q1 runs.

When query Q2 attempts to write to table a, table a is immediately locked for reading (L2). Q2 is in the 'waiting for table level lock' phase, i.e. it hasn't started executing the update but L2 is already effective.

As a result, L2 will be held from the start of Q2 all the way while Q1 executes plus, when Q1 is finished, while Q2 executes.

This doesn't make sense. Shouldn't L2 become effective only after Q2 is not waiting on L1 any more?

  • Let's see the SQL statements. – Rick James Feb 11 '17 at 0:07
  • 1
    MyISAM is going away; move to InnoDB, which won't have this problem, but will need some rethinking. – Rick James Feb 11 '17 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.