Thanks Heikki, My testcase is not valid. After I get the connection, I didn't set auto commit to false on the connection object, so I was unable to see the committed data. I saw the REPEATABLE READ behavior now, but it's unfortunate to know READ COMMITTED is not supported yet...
--Jianliang -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: 2002?10?8? 9:56 To: Jianliang Zhao; Mark Matthews Cc: [EMAIL PROTECTED] Subject: Re: transaction isolation level Jianliang, I tested that with two mysql clients, and it appeared to work ok. Check with SHOW CREATE TABLE tablename what is the type of your table. Is it MyISAM? Also put the line log to the [mysqld] section of your my.cnf and restart mysqld. Then you will see all received queries in the file 'hostname'.log in your datadir. Check from it what SQL queries your clients actually send to the mysqld server. Regards, Heikki Innobase Oy Below my test: CLIENT A: heikki@hundin:~/mysql/client> mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.53-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> set autocmmit = 0; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'autocmmit = 0' at line 1 mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> create table zhao (a int not null, b int, primary key (a)) type = innodb; Query OK, 0 rows affected (0.11 sec) mysql> insert into zhao values (10, 20); Query OK, 1 row affected (0.00 sec) mysql> commit -> ; Query OK, 0 rows affected (0.00 sec) mysql> update zhao set b = 100; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> CLIENT B: heikki@hundin:~/mysql/client> mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 3.23.53-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from zhao; +----+------+ | a | b | +----+------+ | 10 | 20 | +----+------+ 1 row in set (0.00 sec) mysql> select * from zhao; +----+------+ | a | b | +----+------+ | 10 | 20 | +----+------+ 1 row in set (0.00 sec) mysql> select * from zhao; +----+------+ | a | b | +----+------+ | 10 | 20 | +----+------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from zhao; +----+------+ | a | b | +----+------+ | 10 | 20 | +----+------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from zhao; +----+------+ | a | b | +----+------+ | 10 | 20 | +----+------+ 1 row in set (0.00 sec) mysql> ----- Original Message ----- From: "Jianliang Zhao" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, October 08, 2002 7:40 PM Subject: RE: transaction isolation level Thanks for all your help. Then it's more confusing to me. I opened two MySql windows and set autocommit=0 and then update one column and then issue the command COMMIT in one window, in the other MySql window, I queried the column and saw the change immediately. I also did the same thing with one MySql window and one JDBC client(my testcase), and the testcase can see the change immediately. In either case, instead of issuing COMMIT, if I issue ROLLBACK, the change will be rolled back. (Just to make sure the auto commit is not on). Here is my testcase: import java.sql.*; import java.util.Properties; import com.mysql.jdbc.Driver; public class TestMySql { public static void main(String[] args) throws Exception { Driver.class.getName(); Properties props = new Properties(); props.setProperty("user", "root"); props.setProperty("password", "geneva"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", props); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from testtable"); while(rs.next()) { System.out.println(rs.getInt(1)); } System.out.println("Please update the table TESTTABLE..."); System.in.read(); rs = stmt.executeQuery("select * from testtable"); while(rs.next()) { System.out.println(rs.getInt(1)); } } } -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: 2002?10?8? 0:22 To: Mark Matthews Cc: [EMAIL PROTECTED] Subject: Re: transaction isolation level Hi! ----- Original Message ----- From: "Mark Matthews" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, October 08, 2002 5:07 AM Subject: Re: transaction isolation level > Jianliang Zhao wrote: ... > >>I am connecting to MySql 3.23(innodb) with > >>mysql-connector-java-2.0.14-bin.jar. I set the global transaction > >>isolation level to READ COMMITTED. However, I still couldn't see the > >>committed changes through JDBC client. Does anyone know about this > >>issue? ... > The isolation level of READ_COMMITTED has no effect currently in MySQL, > unless you're using BDB tables...InnoDB runs either as REPEATABLE_READ > or SERIALIZABLE, and converts everthing else to REPEATABLE_READ, because > it can do REPATABLE_READ as fast (or faster) than most other DB's > READ_COMMITTED. I have to correct that BDB always runs at the SERIALIZABLE isolation level. The default for InnoDB is REPEATABLE READ, and you can enhance it to SERIALIZABLE with the SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } SQL command. Since MyISAM uses table locking, we can say that MyISAM always runs at the SERIALIZABLE level. The command SHOW VARIABLES currently shows the MySQL default isolation level as READ-COMMITTED, but it has no effect on any of the table handlers. Version 4.0.5 will probably feature a new InnoDB isolation level READ COMMITTED. I will probably at the same time change the MySQL default isolation level to REPEATABLE READ. You can then lower the InnoDB isolation level by the command SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL ... Users porting from Oracle and other databases will find READ COMMITTED a useful level. > -Mark Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
