Bug #37839 TRANSACTION MODE SERIALIZABLE doesn't change My SELECT query
Submitted: 3 Jul 2008 15:27 Modified: 4 Dec 2008 3:38
Reporter: francois pinchard Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.51b-community-nt, 4.1, 5.1, 6.0 bzr OS:Any (Tested also on Suse10.3)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: LOCK IN SHARE MODE, SELECT, SERIALIZABLE, transaction

[3 Jul 2008 15:27] francois pinchard
Description:
Hello

I want my SELCTS on Table blocked when i'm running a transaction on the orther side.

so, i read Here : 
http://dev.mysql.com/doc/refman/5.0/fr/innodb-transaction-isolation.html

"SERIALIZABLE : This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE. "

so that's what i need. I tryed to change the transaction mode (by modifying my.ini or by SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE).

but it doesn't block my select.

How to repeat:
I changed the transaction mode (by modifying my.ini or by SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE).

I star 2 query browser (with MysqlAdministrator for windows). 
on one side, i do :
   START TRANSACTION 
   UPDATE ts_utilisateur_uti SET uti_log = 'BBBB' WHERE uti_id =38;
   //i don't do commit, neither rollback..

and on the other side, i do:
   SELECT * FROM ts_utilisateur_uti

my Select Result display on the screen....

But if i do :
   SELECT * FROM ts_utilisateur_uti LOCK IN SHARE MODE
the result doesn't display until i do a ROLLBACK or a COMMIT on the first side.

so, SERIALIZABLE transaction level the  doesn't appear to add "LOCK IN SHARE MODE " automaticaly to all select queryes

how can you explain it? (Tested also on linux Suse MySQL'5.0.26')
[3 Jul 2008 17:14] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with current development sources using command line client. Also I see no START TRANSACTION before SELECT in your description. This can be cause of the problem also.

Please try current version 5.0.51b. Also pleas try to repeat the problem in MySQL command line client and if problem still exists provide output of SHOW CREATE TABLE ts_utilisateur_uti and copy-paste from your terminal sessions.
[4 Jul 2008 9:33] francois pinchard
Hi.

I've just installed the 5.0.51b and i have the same problem by testing through 2 Terminal msDOS with mysql command line.

here is the create statment of my table :
CREATE TABLE `ts_utilisateur_uti` (
  `UTI_ID` int(11) NOT NULL auto_increment,
  `UTI_Log` varchar(20) default NULL,
  `UTI_Pwd` varchar(20) default NULL,
  `UTI_Nom` varchar(80) default NULL,
  `UTI_Mail` varchar(80) default NULL,
  `UTI_Tel` varchar(20) default NULL,
  `UTI_Fonc` varchar(20) default NULL,
  `UTI_Inactif` tinyint(4) default NULL,
  UNIQUE KEY `UTI_ID` (`UTI_ID`),
  UNIQUE KEY `UTI_Log` (`UTI_Log`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1

But i think that my SELECT should be Blocked by any transaction started on my table, without a "START TRANSACTION" Before my select as you said.

as it is axplained in mySql-doc : "This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE."
[7 Jul 2008 10:47] Sveta Smirnova
Thank you for the feedback.

You are right: having or not START TRANSACTION before SELECT should not affect its behavior.

Workaround:

run START TRANSACTION before SELECT

Test case:

--source include/have_innodb.inc

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

connect (addconroot, localhost, root,,);
connection addconroot;

create table t1(f1 int) engine=innodb;

insert into t1 values(1);

connect (addconroot1, localhost, root,,);
connection addconroot1;

start transaction;

update t1 set f1 = 2 where f1 = 1;

connection addconroot;

show variables like '%isolation';

select * from t1;
[7 Jul 2008 13:08] francois pinchard
Hi, 

OK, YOU where Right : if i add a START TRANSACTION Before my select, it stop the select until rollback or commit on the 1st transaction.

so, i don't understand exactly your last post...
-Do you mean that i have to add a START TRANSACTION Before Each of my SELECT??? (it is the same work to add LOCK IN SHARE MODE after all SELECT)

-Is there a mistake in MySQL-DOC, Or is it a MYSQL Bug?

-i tryed to run your script in mySql MsDos Command, but some Syntax didn't work (Connection() and Connect). so i tested it on 2 MsDos Terminal, and i get exactly the same problem.
[7 Jul 2008 14:32] francois pinchard
Ok I'm sorry : I didn't saw the statut changed to "verified".

And the START TRANSACTION is an "Workaround".

Now I understand and scuse me for my poor english speaking. ;-)
[7 Jul 2008 17:07] Heikki Tuuri
This is not a bug. A standalone SELECT is SERIALIZABLE as a consistent read. Please see the comment below:

ha_innodb.cc in 5.1:

                if (trx->isolation_level == TRX_ISO_SERIALIZABLE
                        && prebuilt->select_lock_type == LOCK_NONE
                        && thd_test_options(thd,
                                OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) {

                        /* To get serializable execution, we let InnoDB
                        conceptually add 'LOCK IN SHARE MODE' to all SELECTs
                        which otherwise would have been consistent reads. An
                        exception is consistent reads in the AUTOCOMMIT=1 mode:
                        we know that they are read-only transactions, and they
                        can be serialized also if performed as consistent
                        reads. */

                        prebuilt->select_lock_type = LOCK_S;
                        prebuilt->stored_select_lock_type = LOCK_S;
                }
[7 Jul 2008 18:05] Sveta Smirnova
Reclassified as documentation issue as I believe this should be clear from the user manual too.
[8 Jul 2008 6:42] francois pinchard
Ok thank you for answeare.

So, if it's not a bug, how can i do to make MySQL work like MsSQL Serveur (to stop my select until the transaction is over on the other side)?
[4 Dec 2008 3:06] Paul DuBois
To make your SELECT block in SERIALIZABLE isolation level, you need to have autocommit disabled. You can do that in either of these ways:

SET autocommit = 0;
SELECT ...

or

START TRANSACTION;
SELECT ...

Then the SELECT will block until your other transaction commits or rolls back.
[4 Dec 2008 3:38] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated description for SERIALIZABLE isolation level:

This level is like REPEATABLE READ, but InnoDB implicitly converts
all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if
autocommit is disabled. If autocommit is enabled, the SELECT is its
own transaction. It therefore is known to be read only and can be
serialized if performed as a consistent (non-locking) read and need
not block for other transactions. (This means that to force a plain
SELECT to block if other transactions have modified the selected
rows, you should disable autocommit.)