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: | |
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
[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.)