Re: transaction isolation level

[sheeri kritzer]

Apologies for a late reply.

1) The query that tries to insert the invalid entry into Table2 fails.
Therefore, if you have 3 separate queries as in the first case, the
last one fails, but the first 2 are successful.  In the second case,
they're all in one query, and if one fails, they all fail.

For optimization purposes, MySQL doesn't turn a query in extended
insert syntax into multiple queries.  The whole point of extended
insert is that it batch processes inserts faster than individual
inserst.

2) Sure, there are plenty of ways. Look up IF(), user variables, and
most importanly, ROLLBACK.  Basically, you want to find out if the
inserts were successful, and if any one insert wasn't successful, you
rollback your transaction.  Your example never actually has a decision
point where you decide whether or not to commit or rollback.

3) There is no way to figure out which value to be inserted made an error.

On 6/14/06, Konrad Baginski <[EMAIL PROTECTED]> wrote:

Hi.

I have a few questions regarding the transaction levels in mysql 5.0.20
using InnoDB tables.
we are trying to populate two tables in the two following ways, we thought
that they would  be equivalent, apparently they are not.
have a look at the following (questions last).



FIRST METHOD:


create database test10;
use test10;


DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  logid VARCHAR(32) NULL,
  PRIMARY KEY(id),
  UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;


DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  table1id BIGINT,
  PRIMARY KEY(id),
  FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;


START TRANSACTION;

INSERT INTO Table1(logid) VALUES('1');
INSERT INTO Table1(logid) VALUES('2');

COMMIT;

START TRANSACTION;

INSERT INTO Table2(table1id) VALUES('1');
INSERT INTO Table2(table1id) VALUES('2');
INSERT INTO Table2(table1id) VALUES('3');

COMMIT;


select * from Table1; select * from Table2;

++---+
| id | logid |
++---+
| 1  | 1 |
| 2  | 2 |
++---+
2 rows in set (0.00 sec)

++--+
| id | table1id |
++--+
| 1  | 1|
| 2  | 2|
++--+
2 rows in set (0.00 sec)


### END FIRST METHOD ###





SECOND METHOD:


create database test10;
use test10;

DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  logid VARCHAR(32) NULL,
  PRIMARY KEY(id),
  UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;


DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  table1id BIGINT,
  PRIMARY KEY(id),
  FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;



START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1'), ('2');
COMMIT;

START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1'), ('2'), ('3');
COMMIT;



select * from Table1; select * from Table2;
++---+
| id | logid |
++---+
| 1  | 1 |
| 2  | 2 |
++---+
2 rows in set (0.00 sec)

Empty set (0.00 sec)


### END SECOND METHOD ###




Questions
1. Why are the two ways of adding rows not equivalent, after all, they both
happen in a trancation?
2. Is there some way to make both of them either add the two first rows to
Table2 or not to add any row?

3. If we look at the second method to insert values, how can i find out
exacly which of the values made an error?
   (in this case, the third value has no matching row in Table1).




/konrad baginski



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction isolation level - potential bug?

[Mark Matthews]

Amine Korch wrote:
> Hello all.
> I have encountered an really annoying problem in MySQL 3.23.49 on Win32.
> I am using InnoDB because I need transactions. I use autocommit off. 
> I'll try best to describe the problem I have: 
> I have two sessions to my DB. 
> Session 1 inserts some data into a table, then commits. 
> Session 2 should normally be able to see data inserted by session 1,
> since session 1 has committed. But it is not the case. 
> The only way I manage to get session 2 to see data inserted at session 1
> is by doing a commit. Is this what should happen?
> 
> Please correct me if I am wrong, but using the READ_COMMITTED
> transaction isolation level, I should be achieving what I want, right? 
> I used the following syntax to ensure that the read_committed level
> (which is apparently default to Mysql) is enforced:
> set global transaction isolation level read_commited;
> 
> I tried using MySQL v4 beta, but same thing happens.
> Thanks for any clarification on what I am doing wrong. 
> 
> 
> -
> 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
> 

InnoDB does not support an isolation level of READ_COMMITTED, only 
REPEATABLE_READ. You will get REPEATABLE_READ when you ask for 
READ_COMMITTED. In most cases this is desirable, because it is easier to 
develop for an isolation level of REPEATABLE_READ, and InnoDB can 
provide this higher isolation level at a higher speed than most 
databases can provide READ_COMMITTED. (see 
http://www.innodb.com/ibman.html#InnoDB_transaction_model)

Heikki has said that support for READ_COMMITTED will come in MySQL-4.0.5.

-Mark
-- 
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ 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

RE: transaction isolation level

[Jianliang Zhao]

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

Re: transaction isolation level

[Heikki Tuuri]

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 

RE: transaction isolation level

[Jianliang Zhao]

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

Re: transaction isolation level

[Heikki Tuuri]

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

Re: transaction isolation level

[Mark Matthews]

Jianliang Zhao wrote:

>Thanks Jeremy. It turns out the JDBC SQL query tool(ViennaSQL) I am trying is causing 
>the problem. I couldn't reproduce the problem by writing a test case.
>
>Thanks,
>
>Jianliang
>
>-Original Message-
>From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
>Sent: 2002?10?7? 17:36
>To: Jianliang Zhao
>Cc: [EMAIL PROTECTED]
>Subject: Re: transaction isolation level
>
>
>On Mon, Oct 07, 2002 at 05:30:57PM -0700, Jianliang Zhao wrote:
>  
>
>>Hi,
>>
>>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?
>>
>>
>
>Can you provide a sample test case, maybe?
>  
>
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.

-Mark

-- 
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ 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

RE: transaction isolation level

[Jianliang Zhao]

Thanks Jeremy. It turns out the JDBC SQL query tool(ViennaSQL) I am trying is causing 
the problem. I couldn't reproduce the problem by writing a test case.

Thanks,

Jianliang

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?7? 17:36
To: Jianliang Zhao
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level


On Mon, Oct 07, 2002 at 05:30:57PM -0700, Jianliang Zhao wrote:
> Hi,
> 
> 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?

Can you provide a sample test case, maybe?
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 62 days, processed 1,334,315,423 queries (246/sec. avg)

-
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

Re: transaction isolation level

[Jeremy Zawodny]

On Mon, Oct 07, 2002 at 05:30:57PM -0700, Jianliang Zhao wrote:
> Hi,
> 
> 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?

Can you provide a sample test case, maybe?
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 62 days, processed 1,334,315,423 queries (246/sec. avg)

-
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