Lock error in Innodb(READ-COMMITED)

Description:
Hello Sinisa,

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

Yes but I am using READ-COMMITTED as transaction level and
InnoDB manual says:
-READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, NOT the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it.

And my table was:
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').

So, this select:
>Begin
>Select * from Sitios where Cod>'AA' and Cod<'ZZ'

does not return records, so it cannot lock anohter user trying
the same transaction.(in REPEATABLE-READ transaction level YES
because it can lock gaps between records but NOT in READ-COMMITED
because in this level gaps are not locked).

Regards,
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: medium
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

Bug or feature??? Timestamp field contains time of the beginning of the transaction

Hi,

I have found one unclear place for me regarding to the timestamp field.
The TIMESTAMP field contains transaction's beginning time,
but I would expect to have there time when transaction finished.

Is it bug or feature?

How to reproduce it...

Let's say we have a table Test with 'timestamp' field:

*******
CREATE TABLE `Test` (
`id` int(3) NOT NULL auto_increment,
`UpdateDate` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;

insert into Test values (1, NULL);
*******

and I try to update one row in this table:

*******
update Test set UpdateDate = NULL where id = 1;
*******

Let's say I started 'update' statement at 15:00:00. But the row with id = 1
is blocked by another transaction ("LOCK TABLES Test WRITE;"),
so 'update' statement needs to wait till the end of the transaction.
After 10 sec the block on the record with id = 1 is released ("UNLOCK
TABLES;").
So my 'update' finished.

select UpdateDate from Test where id = 1

gives me 20030728150000, but I would expect 20030728150010.

Thanks in advance.

Mikhail.


--
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: Bug or feature??? Timestamp field contains time of the beginning of the transaction

Hi!

On Jul 28, Mikhail Entaltsev wrote:
> Hi,
>
> I have found one unclear place for me regarding to the timestamp field.
> The TIMESTAMP field contains transaction's beginning time,
> but I would expect to have there time when transaction finished.
>
> Is it bug or feature?
>
> Let's say I started 'update' statement at 15:00:00. But the row with id = 1
> is blocked by another transaction ("LOCK TABLES Test WRITE;"),
> so 'update' statement needs to wait till the end of the transaction.
> After 10 sec the block on the record with id = 1 is released ("UNLOCK
> TABLES;").
> So my 'update' finished.
>
> select UpdateDate from Test where id = 1
>
> gives me 20030728150000, but I would expect 20030728150010.
>
> Thanks in advance.
>
> Mikhail.

The following is the opinion of Peter Gulutzan - our SQL expert and the
author of "SQL Complete, Really" book:

The SQL standard says we can do it either way:
"The time of evaluation of the
during the execution of the SQL-statement is
implementation-dependent". Of course this is not
a reference to a TIMESTAMP column, it's to functions
like CURRENT_TIMESTAMP, but such functions are implied.

Suppose that with MySQL version 5 we can say
"IF CURRENT_TIME < '12:00:00' THEN
INSERT INTO TIMESTAMP_COLUMN VALUES (NULL) END IF"

Suppose the time is 11:59:59.

Suppose that the user's scenario occurs, that is,
the insert starts but is locked out for ten seconds.
Then, if we we do what the user wants and put the
latest possible value in timestamp_column, it will
contain '... 12:00:09'. In other words, we would
have a value that the IF condition was supposed to
avoid.

In fact, the SQL standard prohibits such a thing:
if there is more than one reference to a current
time, then "all such references are effectively
evaluated simultaneously". In that case, to be
compatible, MySQL absolutely cannot wait to
calculate the time until after the lock ends.
It must do so during the IF condition test,
and whatever value it finds during the IF
condition test is the value it must use during
the INSERT.

Let us keep MySQL's current behaviour.

Regards,
PeterG

Peter Gulutzan, Software Architect
MySQL AB, www.mysql.com

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.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