Lock error in Innodb(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:Subject:DeadLock bug using mysql/Innodb

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(READ-COMMITED)

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

Hi!

This is expected behaviour as per our manual, as FOR UPDATE lock's
index records.

--=20

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=3Dm=
smi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic >
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinat=
or
<___/ www.mysql.com Larnaca, Cyprus


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