8 messages in com.mysql.lists.javaRE: UNLOCK TABLES and connection pooling
FromSent OnAttachments
Michael McTernan24 Feb 2004 09:48 
Sao Many24 Feb 2004 19:24 
Marc Slemko24 Feb 2004 19:36 
Mark Matthews25 Feb 2004 00:07 
Marc Slemko25 Feb 2004 00:29 
Mark Matthews25 Feb 2004 00:42 
Michael McTernan25 Feb 2004 02:55 
Michael McTernan25 Feb 2004 02:58 
Subject:RE: UNLOCK TABLES and connection pooling
From:Michael McTernan (Mich@ttpcom.com)
Date:02/25/2004 02:55:19 AM
List:com.mysql.lists.java

Hi there,

I've already found that closing a connection (i.e. Connection.close()) on a pooled connection doesn't cause TEMPORARY TABLES to get deleted.

and even worse, in 4.0 you can't drop temporary tables unless you have the "drop tables" permission. I think 4.1 adds a drop temporary tables permission to fix this.

I found the permission problem too, and bought it up either here or on mys@lists.mysql.com a few months ago. I currently add a "DROP TEMPORARY TABLE IF EXISTS ....;" prior to using any temporary table, and also attempt to delete them in finally clauses as a workaround.

Can anyone confirm that Connection.close() will, or will not, implicitly UNLOCK TABLES, if any tables have been locked?

[snip]

Nope, it doesn't unlock tables unless the connection to mysql is actually closed. A workaround could be, when checking the connection in (ie. inside the connection pool code, or some hook provided by it), to always execute an unlock tables command, just to make sure.

Hmm. I think I'll make a Connection wrapper object and make close() to try an "UNLOCK TABLES;" when it is called. I could also issue a rollback() and set the commit mode to auto at the same time, incase it isn't. I already made a Connection wrapper to report is a Connection object is finalized before being closed (i.e. leaked), so this should be a simple extension.

Thanks,

Mike

-----Original Message----- From: Marc Slemko [mailto:mar@znep.com] Sent: 25 February 2004 03:37 To: Michael McTernan Cc: ja@lists.mysql.com Subject: Re: UNLOCK TABLES and connection pooling

On Tue, 24 Feb 2004, Michael McTernan wrote:

Hi there,

I've using the standard connection pooling with Tomcat 5.0.16 and MySQL via Connector/J 3.0.

I've already found that closing a connection (i.e. Connection.close()) on a pooled connection doesn't cause TEMPORARY TABLES to get deleted.

and even worse, in 4.0 you can't drop temporary tables unless you have the "drop tables" permission. I think 4.1 adds a drop temporary tables permission to fix this.

Can anyone confirm that Connection.close() will, or will not, implicitly UNLOCK TABLES, if any tables have been locked?

If it doesn't implicitly unlock tables, it makes exception handling very nasty indeed :(

Nope, it doesn't unlock tables unless the connection to mysql is actually closed. A workaround could be, when checking the connection in (ie. inside the connection pool code, or some hook provided by it), to always execute an unlock tables command, just to make sure.

Also note get_lock(), if you use it, has the same behaviour. A workaround could be, when checking the connection back in, to execute a get_lock on a temporary lock used only for this purpose, which implicity releases any other locks, and then release that. That overhead may or may not be acceptable, depending on your application.

I really don't recommend anyone use explicit table locks or get_lock() with mysql at all for any application where robustness is required unless other requirements preclude using innodb and its row locks.

There are other per connection options that may not be properly reset as well, like transaction isolation level.

I wish mysql had some "reset connection state" call. It isn't clear if that would actually provide any performance boost over closing the connection and reopening it though, ie. not using connection pooling. Connections are fairly lightweight in mysql to begin with.