Date : Tue, 9 Nov 2004 18:35:40 +0100
De : Julien ALLANOS <[EMAIL PROTECTED]>
Adresse de retour :Julien ALLANOS <[EMAIL PROTECTED]>
Sujet : Re: Read locks with InnoDB?
À : Gleb Paharenko <[EMAIL PROTECTED]>
> Julien ALLANOS <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > Basically, I have multiple MySQL clients that have to delete entries from=
> > a
> > InnoDB table in a concurrent environment. What they do is:
> > 1 - getting the first entry of the table that needs to by deleted, by exe=
> > cuting
> > a "SELECT ... LIMIT 1" query,
> > 2 - doing stuff with the query result,
> > 3 - deleting the entry from the table if everything went right.
> >
> > However, let's say user A has executed the SELECT query and starts "doing
> > stuff". Before A has deleted the entry from the table, user B executes th=
> > e
> > SELECT query: he gets the same result as A, and starts doing the *same* s=
> > tuff
> > as A, which is something I need to avoid.
> >
> > How could I achieve this? Is it possible to have blocking SELECT queries,=
> > or to
> > set read locks on one row (without locking all the table, to let others u=
> > sers
> > purge the following entries)?
> >
> > Thanks for feedback.
> > --
> > Julien ALLANOS
> > Silicomp-AQL
Selon Gleb Paharenko <[EMAIL PROTECTED]>:
> Hello.
>
> Transactions are usually used for such purposes, just as
> 'select ... for update'.
>
> You may have interest in:
>
> http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html
> http://dev.mysql.com/doc/mysql/en/Transactional_Commands.html
>
Thanks, I've already read these pages.
Here is a test example I've done:
1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
3/ User A: START TRANSACTION;
4/ User B: START TRANSACTION;
5/ User A: SELECT * FROM MyTable LIMIT = 1;
I got a one-row result.
6/ User B: SELECT * FROM MyTable LIMIT = 1;
I got the same one-row result, and I need to get the next one instead, because
this one is already being treated by user A!
I really want to have a "SELECT FOR DELETE" thing: as soon as a row is read, it
is deleted. Is this possible?
--
Julien ALLANOS
Silicomp-AQL
************************************************************
The contents of this email and any attachments are
confidential. They are intended for the named recipient(s)
only.
If you have received this email in error please notify the
system manager or the sender immediately and do not disclose
the contents to anyone or make copies.
* email scanned for viruses, vandals and malicious content *
************************************************************
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
