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

Discussion in 'Java' started by christopher@dailycrossword.com, Mar 7, 2008.

  1. Guest

    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!
     
    , Mar 7, 2008
    #1
    1. Advertising

  2. Guest

    Re: is a MySQL write lock automatically released when a pooledconnection is closed?

    On Mar 7, 2:29 pm, wrote:
    > 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!
     
    , Mar 9, 2008
    #2
    1. Advertising

  3. Arne Vajhøj Guest

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

    wrote:
    > On Mar 7, 2:29 pm, wrote:
    >> Greetings,
    >> I am using JDBC (probably version 2), and I use code like this to open
    >> and close connections:


    > 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
     
    Arne Vajhøj, Mar 10, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fuzzyman
    Replies:
    3
    Views:
    532
    Andrew MacIntyre
    Dec 5, 2003
  2. Robert Brewer
    Replies:
    0
    Views:
    520
    Robert Brewer
    Dec 5, 2003
  3. k3xji
    Replies:
    7
    Views:
    877
    Gabriel Genellina
    Dec 30, 2008
  4. nano2k

    Application.Lock()/UnLock() or lock(Application)

    nano2k, Jul 23, 2007, in forum: ASP .Net Web Services
    Replies:
    2
    Views:
    313
    nano2k
    Aug 9, 2007
  5. Matt Kruse
    Replies:
    5
    Views:
    334
    Richard Cornford
    Sep 9, 2003
Loading...

Share This Page