Bug #29129 Resetting general_log while the GLOBAL READ LOCK is set causes a deadlock
Submitted: 15 Jun 2007 7:12 Modified: 2 Aug 2007 17:20
Reporter: Ramil Kalimullin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any

[15 Jun 2007 7:12] Ramil Kalimullin
Description:
Resetting @@general_log right after FLUSH TABLES WITH READ LOCK;
leads to a deadlock.

How to repeat:
SET GLOBAL general_log=ON;
FLUSH TABLES WITH READ LOCK;
SET GLOBAL general_log=OFF; // hangs
[28 Jun 2007 21:45] Marc ALFF
Fixed by the patch for Bug#25422
[17 Jul 2007 9:19] Lu Jingdong
I test some cases that will cause deadlock just like this.
(1)FLUSH TABLES WITH READ LOCK;
   FLUSH LOGS;
(2)SET GLOBAL general_log=OFF;
   FLUSH TABLES WITH READ LOCK;
   SET GLOBAL general_log=ON;

MySQL system creates artificial THD for each of the logs when we use these logs. But as we all know  system doesn't create a new thread to do some operations about logs and it isn't a real thread. So I think it is the reason that causes deadlock and there are two aspects need to be modified.

Firstly, if we want to set general log on or off after we have already flushed tables with read lock in the same thread. We should add a judgement whether the current thread is locked. If it is true, we should return error and otherwise we can continue to do next.
if ((res= wait_if_global_read_lock(thd, 0, 1)))
    return res;
start_waiting_global_read_lock(thd);

Secondly, I think the function "void LOGGER::deactivate_log_handler(THD *thd, uint log_type) " should return a boolean value to show its result.
It can be modified as "bool LOGGER::deactivate_log_handler(THD *thd, uint log_type)" and we also need to modify the corresponding codes.
[20 Jul 2007 2:26] Marc ALFF
Dear Lu Jindong,

Thanks for sharing your observations (new cases of dead locks),
and your analysis.

The usage of artificial threads is indeed causing issues with log tables,
and is addressed in related bug#25422.

Please look at the current patch of bug#25422 for details,
as it fixes and already contains tests cases for the present report (bug#29129).

Again, thanks for sharing your observations and analysis.
[27 Jul 2007 18:40] Marc ALFF
Fixed by Bug#25422
[1 Aug 2007 23:28] Konstantin Osipov
Pushed into 5.1.21
[2 Aug 2007 17:20] Paul DuBois
Noted in 5.1.21 changelog.

Log table locking was redesigned, eliminating several
lock-related problems:
  - Truncating mysql.slow_log in a stored procedure
    after use of a cursor caused the thread to lock.
    (Bug #17876) 
  - Flushing a log table resulted in warnings.
    (Bug #23044)
  - The server would hang when performing concurrent
    ALTER TABLE or TRUNCATE TABLE statements against the 
    log tables. (Bug #25422) 
  - Changing the value of the general_log system
    variable while a global read lock was in place
    resulted in deadlock. (Bug #29129)