Re: Lock error in InnoDB using READ-COMMITED - Heikki Tuuri - com.mysql.lists.bugs

2 messages in com.mysql.lists.bugsRe: Lock error in InnoDB using READ-C...

From	Sent On	Attachments
rafa...@netscape.net	18 Jul 2003 01:04
Heikki Tuuri	21 Jul 2003 03:11

Subject:	Re: Lock error in InnoDB using READ-COMMITED
From:	Heikki Tuuri (Heik...@innodb.com)
Date:	07/21/2003 03:11:09 AM
List:	com.mysql.lists.bugs

Rafa,

----- Original Message ----- From: <rafa...@netscape.net> To: <bu...@lists.mysql.com> 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: <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?promo=393397

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