About mysql++3.1.0 SET TRANSACTION ISOLATION LEVEL

[陈秋丰]

 Hello:
   I foud a question about function of transaction.
   In this function switch not have break

Transaction::Transaction(Connection conn, IsolationLevel level,
   IsolationScope scope, bool consistent) :
conn_(conn),
finished_(true)   // don't bother rolling it back if ctor fails
{
 // Set the transaction isolation level and scope as the user wishes
 Query q(conn_.query(SET ));
 if (scope == session) q  SESSION ;
 if (scope == global)  q  GLOBAL ;
 q  TRANSACTION ISOLATION LEVEL ;
 switch (level) {
   case read_uncommitted: q  READ UNCOMMITTED;
   case read_committed:q  READ COMMITTED;
   case repeatable_read:q  REPEATABLE READ;
   case serializable:  q  SERIALIZABLE;
 }
Qiufeng Chen

陈秋丰
360平台研发部
[说明: 说明: 说明: 说明: 2-1-26 邮件格]

电话：13693389017
飞信：13693389017
邮件：chenqiuf...@360.cn
地址：北京朝阳区建国路71号惠通时代广场C座202 100025

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]

transaction isolation level

[Konrad Baginski]

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]

Re: a question in SERIALIZABLE transaction isolation level

[Gleb Paharenko]

Hello.



SERIALIZABLE is like REPEATABLE READ, but all plain SELECT statements are

implicitly converted to SELECT ... LOCK IN SHARE MODE. In REPEATABLE

READ statements like SELECT ... LOCK IN SHARE MODE that use a unique

index with a unique search condition lock only the index record found,

not the gap before it. So, parallel INSERTs are possible and they cause

a 'duplicate' error.





 In the transaction isolation level,Database send some duplicate entry 

 message to me.

 Why?

 

 _

 $$ MSN Messenger:  http://messenger.msn.com/cn  

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

a question in SERIALIZABLE transaction isolation level

[王 旭]

In the transaction isolation level,Database send some duplicate entry 
message to me.

Why?

_
与联机的朋友进行交流，请使用 MSN Messenger:  http://messenger.msn.com/cn  



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

RE: Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?

[Daniel Gaddis]

Thanks for your reply. Should we move this discussion to
[EMAIL PROTECTED]

 Also, notice that unless your application is under _extreme_ load,
none
 of these SET queries will are likely to have an impact on the
 performance of your application.

I was hoping someone would reply saying that SET SESSION TRANSACTION
ISOLATION LEVEL READ COMMITTED would be ignored for MyISAM tables. I may
open an official support call to verify.

 If you're using a newer version of our JDBC driver (3.1.x), you can
 always add useLocalSessionState=true to avoid having to do _some_ of
 these queries to the database. 

I installed the 3.1.10 driver and tried that but I can not tell a
difference and don't know how to verify. I submitted it to the
coldfusion database access forum for help.

I noticed the MySQL Connector/J Documentation for useLocalSessionState
says...

Should the driver refer to the internal values of autocommit and
transaction isolation that are set by Connection.setAutoCommit() and
Connection.setTransactionIsolation(), rather than querying the database?

Can you elaborate any more on this? From the description it makes it
sound like it wouldn't set autocommit or session transaction isolation
at all. Then again, I wonder if it is saying that yes it will set them
both, it just will not query the database to check its setting before it
does (getting rid of show variables?). Humm.

Note-I also set the global TRANSACTION ISOLATION LEVEL READ COMMITTED
thinking that if it was already set at the global level then it would
not be set at the session level (after the show variables). But that
didn't help.

Thanks,
Daniel

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

Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?

[Daniel Gaddis]

Can you help set me straight? 

I recently upgraded from coldfusion 5 to coldfusion mx 7. In the process
I also switched from odbc to jdbc connections to mysql
(4.0.21-nt-max-log).

From looking at the query log, I'm now getting...

SHOW VARIABLES 

...once per connection

followed by...
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

- followed by one or more... 
^   SELECT
^
^  followed by...
^   SET autocommit=1
^   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
^
- this pattern repeats until the final Quit for the connection.

I can include actual entries from the query log if you would like to see
them.

Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to
MyISAM tables? I am not using transactions and InnoDB or BDB tables.
Since I am not using transactions is this statement irrelevant (and
unneeded)? 

I'm also a little surprised by the SET autocommit=1.  The manual says...

The other non-transactional storage engines in MySQL Server (such as
MyISAM) follow a different paradigm for data integrity called ``atomic
operations.'' In transactional terms, MyISAM tables effectively always
operate in AUTOCOMMIT=1 mode. Atomic operations often offer comparable
integrity with higher performance. 

and...

By default, MySQL runs with autocommit mode enabled. This means that as
soon as you execute a statement that updates (modifies) a table, MySQL
stores the update on disk. 

and...

Note that if you are not using transaction-safe tables, any changes are
stored at once, regardless of the status of autocommit mode.

So it would seem that the SET autocommit=1 commands being seen in the
query log are not needed. I checked and autocommit is enabled (not that
it would seem to matter since I'm using MyISAM tables)...

mysql SELECT @@AUTOCOMMIT;
+--+
| @@autocommit |
+--+
|1 |
+--+

Am I right that neither 

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
SET autocommit=1

are appropriate/needed for MyISAM tables?

If that is true, then I guess my next step is to try and find out why
coldfusion mx 7 is issuing them to MySQL when using MyISAM tables.

Thanks,
Daniel

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

Re: Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Daniel Gaddis wrote:
 Can you help set me straight? 
 
 I recently upgraded from coldfusion 5 to coldfusion mx 7. In the process
 I also switched from odbc to jdbc connections to mysql
 (4.0.21-nt-max-log).
 
 From looking at the query log, I'm now getting...
 
   SHOW VARIABLES 
 
 ...once per connection
 
 followed by...
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 - followed by one or more... 
 ^ SELECT
 ^
 ^  followed by...
 ^ SET autocommit=1
 ^ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
 ^
 - this pattern repeats until the final Quit for the connection.
 
 I can include actual entries from the query log if you would like to see
 them.
 
 Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to
 MyISAM tables? I am not using transactions and InnoDB or BDB tables.
 Since I am not using transactions is this statement irrelevant (and
 unneeded)? 
 
 I'm also a little surprised by the SET autocommit=1.  The manual says...
 
 The other non-transactional storage engines in MySQL Server (such as
 MyISAM) follow a different paradigm for data integrity called ``atomic
 operations.'' In transactional terms, MyISAM tables effectively always
 operate in AUTOCOMMIT=1 mode. Atomic operations often offer comparable
 integrity with higher performance. 
 
 and...
 
 By default, MySQL runs with autocommit mode enabled. This means that as
 soon as you execute a statement that updates (modifies) a table, MySQL
 stores the update on disk. 
 
 and...
 
 Note that if you are not using transaction-safe tables, any changes are
 stored at once, regardless of the status of autocommit mode.
 
 So it would seem that the SET autocommit=1 commands being seen in the
 query log are not needed. I checked and autocommit is enabled (not that
 it would seem to matter since I'm using MyISAM tables)...
 
 mysql SELECT @@AUTOCOMMIT;
 +--+
 | @@autocommit |
 +--+
 |1 |
 +--+
 
 Am I right that neither 
 
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
   SET autocommit=1
 
 are appropriate/needed for MyISAM tables?
 
 If that is true, then I guess my next step is to try and find out why
 coldfusion mx 7 is issuing them to MySQL when using MyISAM tables.

Daniel,

All of those items are _required_ by the JDBC specification. Some of
them are being set by the JDBC driver when you create a new connection
(i.e. SET autocommit=1), others by your connection pool (most likely,
for things like SET SESSION TRANSACTION ISOLATION LEVEL, as the JDBC
spec requires connections newly created or being taken from a pool to be
in a certain state).

Also, notice that unless your application is under _extreme_ load, none
of these SET queries will are likely to have an impact on the
performance of your application.

There's not an easy way to _not_ do them, as software built on top of
JDBC expects those values to be set correctly.

If you're using a newer version of our JDBC driver (3.1.x), you can
always add useLocalSessionState=true to avoid having to do _some_ of
these queries to the database. You'll also want to have your connection
pool hold on to connections for some short amount of time so there's a
potential for re-use, instead of creating a new connection every time.

The show variables query is used by the JDBC driver to configure
various internal things based on what version of MySQL the driver is
connected to, and how you've chosen to configure the server. It can't be
avoided. Once again, if you're using version 3.1.x of the driver, you
can put cacheServerConfiguration=true in your JDBC URL, and the values
from this query will be cached, however if you reconfigure your database
server, you'll need to restart your appserver to pick up the new values.

-Mark

- --
Mark Matthews
MySQL AB, Software Development Manager - Connectivity
www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCzXXntvXNTca6JD8RAq/IAKCQUdX3XxdWGt4232QL1DIGHDwXUACfSzr1
+W6uZKDy+35vYvPBDmrBsJw=
=ZtjH
-END PGP SIGNATURE-

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

Transaction isolation level - potential bug?

[Amine Korch]

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

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

[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

[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]

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

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.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

transaction isolation level

[Jianliang Zhao]

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?

Thanks,

Jianliang

-
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

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

[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: InnoDB and SET TRANSACTION ISOLATION LEVEL, etc...

[Heikki Tuuri]

Jeremy,

look at http://www.innodb.com/ibman.html and http://www.innodb.com/todo.html
about InnoDB isolation levels.

Currently, SET TRANSACTION ISOLATION LEVEL does not have any effect on
MyISAM, BDB, or InnoDB.

The standard isolation level of InnoDB is REPEATABLE READ (you can also call
that READ-COMMITTED with no phantom rows), the same as in Oracle.

By adding ...LOCK IN SHARE MODE (set S-locks) or ...FOR UPDATE (set X-locks)
to all your SELECT statements you get SERIALIZABLE execution in InnoDB. Both
wait if there are conflicting locks set on the rows they look at (to be
precise, index records), for example an the index records of an inserted row
have an X-lock on them until the inserting transaction commits. Both read
the latest data.

I will add the SET TRANSACTION ISOLATION LEVEL feature which lets the user
to change the InnoDB isolation level to SERIALIZABLE. It will map the SQL-92
isolation levels in the following way to InnoDB isolation levels:

READ-UNCOMMITTED  - REPEATABLE READ
READ-COMMITTED - REPEATABLE READ
REPEATABLE READ - REPEATABLE READ
SERIALIZABLE - SERIALIZABLE

I have no plans of adding READ-UNCOMMITTED to InnoDB, because there is no
such level in Oracle either. READ-UNCOMMITTED is usually used in
non-multiversioning databases to avoid excessive locking when you run big
SELECT queries on tables. The penalty you pay in them for READ-UNCOMMITTED
is that your reports may be a bit inconsistent.

The standard InnoDB isolation level REPEATABLE READ is not equivalent to
SERIALIZABLE. Consider the following example: there are two transactions
which both do SELECT COUNT(*) FROM T and INSERT INTO T VALUES (...). Both
may see the row count as 10, if they are running concurrently, and then do
the inserts. But in any serial (= strictly consecutive) execution of these
two transactions the latter should see the row count as 11.

But, if we add LOCK IN SHARE MODE to the SELECT query, then inevitably one
of the transactions will see the count as 11.

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




-Original Message-
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]
Date: Sunday, January 27, 2002 3:52 AM
Subject: InnoDB and SET TRANSACTION ISOLATION LEVEL, etc...


Okay, I'm trying to make sense of several things here (so that I can
clearly document them), so bear with me.

First of all, there was some syntax added to MySQL for setting
transaction isolation levels:

http://www.mysql.com/doc/S/E/SET_TRANSACTION.html

around the time that Gemini/MySQL work first started happening, I
think.

Since InnoDB uses MVCC and row-level locking for transactions, I'm
wondering how that syntax interacts with InnoDB.  Does it have any
effect?  And what isolation levels does it support?

Here's what I think I know.

I'm reading about InnoDB's transaction model:

http://www.mysql.com/doc/I/n/InnoDB_transaction_model.html

and using some PostgreSQL docs for reference:

http://www.postgresql.org/idocs/index.php?transaction-iso.html

The first case is READ-UNCOMMITTED.

InnoDB's doesn't have the capability as far as I can tell.  Is that
true?  No dirty reads?

(PostgreSQL doesn't support READ-UNCOMMITTED.)

Next comes READ-COMMITTED.

http://www.mysql.com/doc/I/n/InnoDB_consistent_read.html

This is InnoDB's default, with non-locking reads and all that good
stuff.  Easy. :-)

(PostgreSQL, of course, supports READ-COMMITTED.)

Then comes REPEATABLE-READ.

http://www.mysql.com/doc/I/n/InnoDB_locking_reads.html

InnoDB handles this via SELECT ... LOCK [IN SHARE MODE|FOR UPDATE].
Or that's what it would appear.  However, I'm a bit confused by the
information on how IN SHARE MODE differs from FOR UPDATE.

On IN SHARE MODE, the docs say:

Performing a read in share mode means that we read the latest
available data, and set a shared mode lock on the rows we read. If
the latest data belongs to a yet uncommitted transaction of
another user, we will wait until that transaction commits. A
shared mode lock prevents others from updating or deleting the row
we have read.

Which makes sense, I think.  We get the *latest available* data,
meaning the most recently committed transaction's data.  But if the
data is part of an open transaction, then we wait for it.  Once we
have the data, we get a shared lock so that nobody can change the data
until we're done.  Right?  Just want to be positive I have this right
(and document it correctly).

On FOR UPDATE, the docs say:

A SELECT ... FOR UPDATE will read the latest available data
setting exclusive locks on each row it reads. Thus it sets the
same locks a searched SQL UPDATE would set on the rows.

This one also reads the *latest available data* but it DOES NOT say if
it will wait for an open transaction