Bug #10374 GET_LOCK does not let connection to close on the server side if it's aborted
Submitted: 5 May 2005 3:02 Modified: 23 Jul 2010 12:59
Reporter: Ledion B Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:4.1.10a,5.0.36BK OS:Any (*)
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: bfsm_2007_03_01
Triage: D4 (Minor) / E4 (High)

[5 May 2005 3:02] Ledion B
Description:
If an application level lock is in use and another thread (connection) wants to get that lock (and is willing to wait a long time), the thread will block. Now, if the connection (of the waiting thread) is closed either gracefully or not the server does not seem to close the connection. When doing a show processlist the query is still active. I think this can lead into max_connections.

How to repeat:

Two connections to the database, and a lock lock1 that is free

Connection1 issues

Select get_lock("lock1", 10000000);  <-- returns immediately

Connection2 issues 

Select get_lock("lock1", 10000000);  <-- will block for 115 days 

close connection2 (assuming you're not running in a single threaded evironment)

on the server run 
show processlist

Suggested fix:
I have not looked at mysql source, but I have extensive experience with multithreaded environment.

If the close of the connection is detected in another thread but the one that runs queries, then an interruption would suffice. Otherwise there needs to be some other mechanism that determines when a connection closes and interrupt the query thread of that connection. Again I don't know how mysql is organized, so feel free to ignore this piece of advice.
[5 May 2005 13:44] Geert Vanderkelen
Hi,

Reproduced your example using 4.1.11 and 5.0 from source.

Not really bug, but might be nice to have some check when connection is gone, to avoid reaching max_connection.. But it is more a application site issue..

I'll let the developers decide here :)

Thanks,

Geert
[27 Sep 2005 9:39] Adam Newby
This would seem to me to be a bug, rather than a feature. On our system, we've had this happen when a machine which is connected and is holding a user lock reboots. In that circumstance, the MySQL server doesn't drop the connection, and the lock is then held for an extremely long period (presumably as long as wait_timeout, default 28800 seconds), blocking all other threads from acquiring it, and requiring intervention (by killing the thread holding the lock) to rectify matters.

It seems nonsensical for it to be possible for a lock to be held by a process which was running on a machine which is now offline!
[21 Jan 2006 0:25] Paul Dubois
I have documented this behavior as a bug in the description
for GET_LOCK(). I have left this report as Verified and have
unassigned myself from it so that it can be reassigned to
a developer to be fixed.
[3 Jul 2007 18:59] Kolbe Kegel
As soon as the original lock is released, the dead connection will "get" the lock, and when that happens it will be immediately released and the connection will disappear. Are you seeing something different? The connection will also disappear as soon as the timeout value is reached.

A solution in this case is to avoid using an extraordinarily high wait time for the GET_LOCK() function.

A dead connection will wait no longer and cause no problems than a live connection, as far as I can tell. If I am mistaken, please clarify.
[19 Mar 2008 0:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44210

ChangeSet@1.2604, 2008-03-18 21:49:09-03:00, davi@mysql.com +8 -0
  Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted
  
  The problem is that the server doesn't detect aborted connections which
  are waiting on a lock or sleeping (user sleep), wasting precious system
  resources and also a potential DOS vector.
  
  The solution is to peek at the connection every five seconds to verify
  if the connection is not aborted.
[19 Mar 2008 14:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44227

ChangeSet@1.2601, 2008-03-19 11:04:50-03:00, davi@mysql.com +8 -0
  Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted
  
  The problem is that the server doesn't detect aborted connections which
  are waiting on a lock or sleeping (user sleep), wasting system resources
  for a connection that is already dead.
  
  The solution is to peek at the connection every five seconds to verify if
  the connection is not aborted. A aborted connection is detect by polling
  the connection socket for available data to be read or end of file. In
  case of eof, the wait is aborted and the connection killed.
[27 Mar 2008 20:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44535

ChangeSet@1.2608, 2008-03-27 17:22:36-03:00, davi@mysql.com +7 -0
  Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted
  
  The problem is that the server doesn't detect aborted connections which
  are waiting on a lock or sleeping (user sleep), wasting system resources
  for a connection that is already dead.
  
  The solution is to peek at the connection every five seconds to verify if
  the connection is not aborted. A aborted connection is detect by polling
  the connection socket for available data to be read or end of file and in
  case of eof, the wait is aborted and the connection killed.
[28 Mar 2008 12:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44581

ChangeSet@1.2608, 2008-03-28 09:36:26-03:00, davi@mysql.com +7 -0
  Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted
  
  The problem is that the server doesn't detect aborted connections which
  are waiting on a lock or sleeping (user sleep), wasting system resources
  for a connection that is already dead.
  
  The solution is to peek at the connection every five seconds to verify if
  the connection is not aborted. A aborted connection is detect by polling
  the connection socket for available data to be read or end of file and in
  case of eof, the wait is aborted and the connection killed.
[28 Mar 2008 12:39] Davi Arnaut
Queued to 6.0-runtime
[20 Apr 2008 13:01] Bugs System
Pushed into 6.0.6-alpha
[23 Apr 2008 17:26] Paul Dubois
Noted in 6.0.6 changelog.

If a connection was waiting for a GET_LOCK() lock or a SLEEP() call,
and the connection aborted, the server did not detect this and thus
did not close the connection. This caused a waste of system resources
allocated to dead connections. Now the server checks such a 
connection every five seconds to see whether it has been aborted. If
so, the connection is killed (and any lock request is aborted).
[17 Jun 2009 19:28] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:11)
[29 Oct 2009 8:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88563

2900 Alexander Nozdrin	2009-10-29
      Backporting patch for Bug#10374 from 6.0.
      Original revision:
      ------------------------------------------------------------
      revno: 2597.37.3
      revision-id: sp1r-davi@mysql.com/endora.local-20080328123626-16430
      committer: davi@mysql.com/endora.local
      timestamp: Fri 2008-03-28 09:36:26 -0300
      message:
        Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted
        
        The problem is that the server doesn't detect aborted connections which
        are waiting on a lock or sleeping (user sleep), wasting system resources
        for a connection that is already dead.
        
        The solution is to peek at the connection every five seconds to verify if
        the connection is not aborted. A aborted connection is detect by polling
        the connection socket for available data to be read or end of file and in
        case of eof, the wait is aborted and the connection killed.
      ------------------------------------------------------------
[10 Nov 2009 19:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89998

2920 Davi Arnaut	2009-11-10
      Backport of Bug#10374 to mysql-next-mr
      ------------------------------------------------------------
      revno: 2597.37.3
      revision-id: sp1r-davi@mysql.com/endora.local-20080328123626-16430
      parent: sp1r-anozdrin/alik@quad.opbmk-20080327125300-11290
      committer: davi@mysql.com/endora.local
      timestamp: Fri 2008-03-28 09:36:26 -0300
      message:
        Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted
      
        The problem is that the server doesn't detect aborted connections which
        are waiting on a lock or sleeping (user sleep), wasting system resources
        for a connection that is already dead.
      
        The solution is to peek at the connection every five seconds to verify if
        the connection is not aborted. A aborted connection is detect by polling
        the connection socket for available data to be read or end of file and in
        case of eof, the wait is aborted and the connection killed.
     @ include/violite.h
        Export vio_peek_read function.
     @ mysql-test/r/dirty_close.result
        Add test case result for Bug#10374
     @ mysql-test/t/dirty_close.test
        Add test case for Bug#10374
     @ sql/item_func.cc
        While waiting for a condition to be signaled, check if the connection
        is not broken every INTERRUPT_INTERVAL seconds.
     @ sql/sql_class.cc
        Add function which checks if the client connection was aborted.
     @ sql/sql_class.h
        Add function prototype.
     @ vio/viosocket.c
        Add poll and peek functions for Windows and Unix.
[10 Nov 2009 19:38] Davi Arnaut
Queued to mysql-next-mr-runtime
[20 Nov 2009 12:54] Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:13)
[20 Nov 2009 12:58] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:13)
[26 Nov 2009 15:05] Paul Dubois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 11:08] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:16)
[6 Mar 2010 18:23] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.
[23 Jul 2010 12:26] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)