Lock error in InnoDB using READ-COMMITED

Description:
Hello,

I am using MySQL 4.0.13 with InnoDB and Isolation level=READ-COMMITED.
In that level, a sql sentence like:
Select * from tabla for update
will only lock existing rows.

But suppose the following table:

create table `test`.`Sitios` (
`Cod` char (2) NOT NULL ,
`Nom` varchar (255) NOT NULL ,
PRIMARY KEY ( `Cod` )

Insert into Sitios values('AA','Cordoba'), ('ZZ','Malaga').

Now two users try to do a select for update:

User1 User2
----- -----
Begin .
Select * from Sitios .
where Cod>’ZZ’ for update Begin
--it doesn´t return records,so Select * from Sitios where
--there isn´t any lock. Cod>'ZZ' for update
--Not locked: Okey.

But in this case:

User1 User2
----- -----
Begin .
Select * from Sitios .
where Cod<’AA’ for update Begin
--it doesn´t return records, Select * from Sitios where
--but it locks user2!! Cod<'AA' for update
--Locked!!!

And if both users execute the following SQL, user2 remains
locked too:
Select * from Sitios where Cod>'AA' and Cod<'ZZ'.

This SQL doesn´t return records so it shouldn´t lock user2. But
it locks user2 although READ_COMMITED shouldn´t lock gaps.


Thanks in advance,
Rafa

How-To-Repeat:
Select * from sitios where Cod<'AA' for update

Fix:
-

Synopsis:Lock error in InnoDB using READ-COMMITED

Submitter-Id:
Originator: Rafa
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: -
Category: mysqld-max-nt
Class: sw-bug
Release: mysqld 4.0.13

Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 750 MB
System: Windows 2000
Compiler: -
Architecture: i


____________________________________________________________ ______
McAfee VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?p romo=393397

Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Lock error in InnoDB using READ-COMMITED

Rafa,

----- Original Message -----
From:
To:
Sent: Friday, July 18, 2003 11:04 AM
Subject: Lock error in InnoDB using READ-COMMITED


> Description:
> Hello,
>
> I am using MySQL 4.0.13 with InnoDB and Isolation level=READ-COMMITED.
> In that level, a sql sentence like:
> Select * from tabla for update
> will only lock existing rows.
>
> But suppose the following table:
>
> create table `test`.`Sitios` (
> `Cod` char (2) NOT NULL ,
> `Nom` varchar (255) NOT NULL ,
> PRIMARY KEY ( `Cod` )
>
> Insert into Sitios values('AA','Cordoba'), ('ZZ','Malaga').
>
> Now two users try to do a select for update:
>
> User1 User2
> ----- -----
> Begin .
> Select * from Sitios .
> where Cod>’ZZ’ for update Begin
> --it doesn´t return records,so Select * from Sitios where
> --there isn´t any lock. Cod>'ZZ' for update
> --Not locked: Okey.
>
> But in this case:
>
> User1 User2
> ----- -----
> Begin .
> Select * from Sitios .
> where Cod<’AA’ for update Begin
> --it doesn´t return records, Select * from Sitios where
> --but it locks user2!! Cod<'AA' for update
> --Locked!!!
>
> And if both users execute the following SQL, user2 remains
> locked too:
> Select * from Sitios where Cod>'AA' and Cod<'ZZ'.
>
> This SQL doesn´t return records so it shouldn´t lock user2. But
> it locks user2 although READ_COMMITED shouldn´t lock gaps.

when InnoDB scans the records it does not know when MySQL is going to end
the scan. That is why the record at the end of the scan area gets locked. It
is not a gap lock, but locks the record itself.

TODO: let MySQL communicate also the end of the range to InnoDB, or let
MySQL release the InnoDB record lock if the row did not match to all search
conditions.

> Thanks in advance,
> Rafa

Thank you,

Heikki


> How-To-Repeat:
> Select * from sitios where Cod<'AA' for update
>
> Fix:
> -
>
> Synopsis:Lock error in InnoDB using READ-COMMITED
>
> Submitter-Id:
> Originator: Rafa
> Organization: Pecomark
> MySQL support: none
> Severity: non-critical
> Priority: -
> Category: mysqld-max-nt
> Class: sw-bug
> Release: mysqld 4.0.13
>
> Exectutable: mysqld-max-nt
> Environment: Pentium III-MMX, 500 MHZ, 750 MB
> System: Windows 2000
> Compiler: -
> Architecture: i
>
>
> ____________________________________________________________ ______
> McAfee VirusScan Online from the Netscape Network.
> Comprehensive protection for your entire computer. Get your free trial
today!
> http://channels.netscape.com/ns/computing/mcafee/index.jsp?p romo=393397
>
> Get AOL Instant Messenger 5.1 free of charge. Download Now!
> http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
>
> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?unsub=lenz@mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org