Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: Lock table failure when autocommit false.

    I'm having a problem with Sybase 12.5 connecting from Java using the JConnect 2 driver.

    For some reason, jdbc doesn't seem to be starting transactions correctly when setAutoCommit(false) is set. The following is an except of my code:

    Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
    Connection conn = DriverManager.getConnection("jdbc:sybase:Tds:serve r:4100/database", "user", "password");
    conn.setAutoCommit(false);

    Statement stmt = conn.createStatement();
    stmt.execute("lock table test in exclusive mode");

    PreparedStatement pst = conn.prepareStatement("insert into test (name, datafield) values (?, ?)");
    pst.setObject(1, "Junk");
    pst.setObject(2, "test");
    pst.executeUpdate();

    pst.close();
    conn.commit();
    conn.close();

    The following is the error I receive.

    com.sybase.jdbc2.jdbc.SybSQLException: A LOCK TABLE command is not allowed outside the scope of a transaction on table 'test' in the database 'master'.

    at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2606)
    at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1983)
    at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(Resu ltGetter.java:69)
    at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybS tatement.java:204)
    at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybS tatement.java:187)
    at com.sybase.jdbc2.jdbc.SybStatement.executeLoop(Syb Statement.java:1670)
    at com.sybase.jdbc2.jdbc.SybStatement.execute(SybStat ement.java:1662)
    at com.sybase.jdbc2.jdbc.SybStatement.execute(SybStat ement.java:774)

    If I call the following
    stmt.execute("BEGIN TRANSACTION");
    before I perform the lock, then things seem to work. According to the JDBC docs, setting autocommit to false is supposed to start transactions by itself. Plus doing this kind of a thing breaks this same code when run against different databases.

    If you could suggest anything, that would be great.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    AutoCommit is handled by Sybase by setting the CHAINED TRANSACTION mode to ON (it is off by default).

    I tried this with a small perl script using DBD:ybase, and I was able to run "lock table" as the first command after I'd turned AutoCommit off.

    Have you tried contacting Sybase about this?

    Michael

  3. #3
    Join Date
    Nov 2003
    Posts
    3
    I havn't tried to contact sybase yet. I am currently only using their developer version of their product in order to see if our product can work with it easily.

    I will try to see if they can help.

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Originally posted by DrWh0
    I havn't tried to contact sybase yet. I am currently only using their developer version of their product in order to see if our product can work with it easily.

    I will try to see if they can help.
    I suggest posting this in the jConnect group at news://forums.sybase.com - you'll probably come across a Sybase engineer or two in that group, and they are likely to help you. The groups can also be accessed via http, though I forger the URL at the moment.

    Michael

  5. #5
    Join Date
    Nov 2003
    Posts
    3
    I found a work around suggested by Joe on the Sybase forums. Here's the details in case anyone else has had this problem.

    >
    > Hi. This is a DBMS problem. The setAutoCommit(false) doesn't
    > start a transaction. It sets the session so that the first thing
    > you do will start a transaction. What is odd is that if you were
    > to do practically anything else, such as 'select 1', which causes
    > zero locking in the DBMS, it would start a transaction and your
    > lock table would succeed. The driver isn't at fault.
    > Joe Weinstein at BEA

    Instead of just locking the table, I am now first executing a dummy 'SELECT 1' statement as he suggested and it works fine.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •