BDB read lock / Transaction isolation

Julien Revel Fri, 25 May 2001 04:33:04 -0700

I have a very annoying problem in a big program, that I reduced to
a small and reproductible java program (see code excerpt below).
The problem is as follows :
I have 2 separate connexions on the same BDB table, and I try to do:

Connexion1: Set autocommit (false)
Connexion1 : INSERT INTO TABLE...
Connexion2: SELECT FROM TABLE
Connexion1: COMMIT

The SELECT statement hangs the program, until the commit has been done
on the first connexion, however, with this code, it never happens.

I use MySQL 3.23.38 on Linux RedHat 6.2, with the last BDB support for this version
I tried to set up the transaction-isolation to READ_UNCOMMITTED (1)
without any success, either from command-line, or inside the SQL code.
transaction-isolation parameter is available since version 3.23.36, but
it does not seem to work with BDB tables !
The isolation level (returned by JDBC driver) seems to be always 8 (SERIALIZABLE),
ie dirty reads, non-repeatable reads, and phantom reads are forbidden.
But I WANT to be able to do phantom reads in my BDB Tables, as stated
in the pseudo-code above.
Of course, I tried the same program with
- Oracle 8
- MyIsam tables
- Set autocommit (true)
And it works fine.

I also tried the same manipulation with 2 command lines mysql consoles,
with same user on same table:
- I do a insert from the first console
- I do a select from the 2nd console: it hangs
- I commit from the first console, and imediately, the second console
displays the results of the select.

I tried to set-up isolation level from console, with no results.

Is it possible to do this with BDB ?
Do I have to use InnoDB Tables in order to do this ?

Need help please....

Actual (java) code extract:

            Statement Stmt = Conn.createStatement();
            Statement Stmt2 = Conn2.createStatement();
            String    sQuery ;

            try
            {
              sQuery = "DROP TABLE tellers" ;
              Stmt.executeUpdate(sQuery);
            }
            catch (Exception e )
            {
            }

            sQuery = "CREATE TABLE tellers ( ";
            sQuery+= "Tid         INT NOT NULL, PRIMARY KEY(Tid),";
            sQuery+= "Bid         INT,";
            sQuery+= "Tbalance    INT)";
            sQuery+="type=BDB" ;
            Stmt.executeUpdate(sQuery);

            // ALWAYS RETURNS 8 !
            iIsolation1 = Conn.getTransactionIsolation() ;
            iIsolation2 = Conn2.getTransactionIsolation() ;

            // DOES NOT WORK !
            sQuery = "Set transaction isolation level READ UNCOMMITTED" ;
            Stmt.executeQuery(sQuery);
            // ALWAYS RETURNS 8 !
            iIsolation1 = Conn.getTransactionIsolation() ;

            sQuery = "Set transaction isolation level READ UNCOMMITTED" ;
            Stmt2.executeQuery(sQuery);
            iIsolation2 = Conn2.getTransactionIsolation() ;

Conn.setAutoCommit(false);

            // Following statement is OK
            sQuery = "INSERT INTO tellers(Tid,Bid,Tbalance) VALUES (10,20,30)";
            Stmt.executeUpdate(sQuery);

            // Following query locks the table
            sQuery = "select * from tellers" ;
            rs = Stmt2.executeQuery(sQuery);

// The program never reaches this line !
Conn.commit();

MySQL_BDB_ReadLock.java

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

BDB read lock / Transaction isolation

Reply via email to