is a MySQL write lock automatically released when a pooled connectionis closed?

[christopher]

Greetings,
I am using JDBC (probably version 2), and I use code like this to open
and close connections:

Connection con=null;
Statement sqlstatement=null;
ResultSet sqlresults=null;
PreparedStatement pStatement=null;
try {
con=DriverManager.getConnection("name of connection pool");
....
} catch (SQLException e) {/*handle error*/);
} finally {
if(con!=null) try { con.close();} catch (SQLException e){}
if(sqlstatement!=null) try { sqlstatement.close();} catch
(SQLException e){}
if(pStatement!=null) try { pStatement.close();} catch
(SQLException e){}
if(sqlresults!=null) try { sqlresults.close();} catch
(SQLException e){}
}

Which I think is like belt and suspenders. In a new connection I will
be locking the table, and I need to be certain it is unlocked when I
am done. I am planning to add the "unlock tables" update to the first
line of the finally block, before the close(). I am wondering what
will happen if there is a hiccup (broken pipe in the connection pooler
or something) -- does the lock ever get released?

} catch (SQLException e) {/*handle error*/);
} finally {
if(con!=null) try {
sqlstatement.execute("UNLOCK TABLES;"); // (or whatever
con.close();
} catch (SQLException e){}
if(sqlstatement!=null) try { sqlstatement.close();} catch
(SQLException e){}
if(pStatement!=null) try { pStatement.close();} catch
(SQLException e){}
if(sqlresults!=null) try { sqlresults.close();} catch
(SQLException e){}
}


This really exposes a fundamental lack of understanding on my part
about the nature of connection pooling -- do temporary tables exist
for the life of pooled connections or just for the current use of the
connector? Variables? When are pooled connections ever actually
closed?

Thanx all!

 

[christopher]

Greetings,
I am using JDBC (probably version 2), and I use code like this to open
and close connections:

snip

I have examined the source for the pooled connection's close() method,
and it simply returns the connection object to a generic pool (Jakarta
commons DBCP and Pool), so it seems as though no reset is ever done to
release locks or close temporary tables, etc., as is is in other
pooled connectors. I have read a number of posts from several years
ago either asking the same question, or declaring that 'table locks'
should never be used in 'robust applications'. I am very concerned
that an uncaught exception or transient network failure would lock the
table indefinitely.

For what it's worth I am re-designing that portion of the application
to produce meaningful (if a little stale) data without using the table
locks.

Cheers!

 

[Arne Vajhøj]

I have examined the source for the pooled connection's close() method,
and it simply returns the connection object to a generic pool (Jakarta
commons DBCP and Pool), so it seems as though no reset is ever done to
release locks or close temporary tables, etc., as is is in other
pooled connectors. I have read a number of posts from several years
ago either asking the same question, or declaring that 'table locks'
should never be used in 'robust applications'. I am very concerned
that an uncaught exception or transient network failure would lock the
table indefinitely.

For what it's worth I am re-designing that portion of the application
to produce meaningful (if a little stale) data without using the table
locks.

LOCK TABLE is a hack. A hack that does not fit well with Java.

InnoDB tables, transactions and a suitable transaction isolation
level.

Arne