List:General Discussion« Previous MessageNext Message »
From:Frederic Belleudy Date:February 5 2008 4:24pm
Subject:Re: lock the row selected by a session and lock those rows for other
sessions
View as plain text  
You said:
It is not locking the entire table.  It's locking the rows you're
selecting.  If you don't want the second session to hang and wait,
then you need to tell it to lock different rows.

Well if you read my message, I dont want another script to select the 
rows that are selected by another opened session but i want other script 
to be able to select other rows without hangin on....

Got me? Yes it's like a pool...

Baron Schwartz wrote:
> (Re-CCing the MySQL list)
>
> It is not locking the entire table.  It's locking the rows you're
> selecting.  If you don't want the second session to hang and wait,
> then you need to tell it to lock different rows.
>
> It might help if you explain what you're trying to accomplish.  It
> sounds like you're trying to build a message queue or something, which
> is a problem that has been solved already.
>
> On Feb 5, 2008 9:44 AM, Frederic Belleudy <fredericb@stripped> wrote:
>   
>> 1) damn, I had to recompile myssql because the innodb option wasnt enabled
>> 2) ok now it seems to work almost perfectly.
>>
>> I'm doing the same query on the first session but the second one is
>> waiting for the other session to commit after the selection.
>> Is there a way to tell mysql not locking the table entirely, just the
>> row from the selection?
>>
>> Tks a lot for your help!!
>>
>>
>> Baron Schwartz wrote:
>>     
>>> 1) is the table InnoDB?
>>> 2) is AUTOCOMMIT on?
>>>
>>> On Feb 5, 2008 8:44 AM, Frederic Belleudy <fredericb@stripped>
> wrote:
>>>
>>>       
>>>> Select for update is not working like the way I expected it:
>>>>
>>>> FIRST SESSION:
>>>> mysql> start transaction;
>>>> Query OK, 0 rows affected (0.00 sec)
>>>>
>>>> mysql> select video_id from videos_innodb where state='QUEUE' limit 5
>>>> FOR UPDATE;
>>>> +----------+
>>>> | video_id |
>>>> +----------+
>>>> |        1 |
>>>> |        2 |
>>>> |        3 |
>>>> |        4 |
>>>> |        5 |
>>>> +----------+
>>>> 5 rows in set (0.00 sec)
>>>>
>>>>
>>>> So, you notice I didn't COMMIT those rows...
>>>>
>>>> Check the second session opened:
>>>>
>>>> mysql> START TRANSACTION;
>>>> Query OK, 0 rows affected (0.00 sec)
>>>>
>>>> mysql> select video_id from videos_innodb where state='QUEUE' limit 5
>>>> FOR UPDATE;
>>>> +----------+
>>>> | video_id |
>>>> +----------+
>>>> |        1 |
>>>> |        2 |
>>>> |        3 |
>>>> |        4 |
>>>> |        5 |
>>>> +----------+
>>>>
>>>>
>>>> Same thing, I dont want anyway other session to be able to get that
>>>> selection until I commit.
>>>> Any other suggestion?
>>>>
>>>> Michael Dykman wrote:
>>>>
>>>>         
>>>>> SELECT .... FOR UPDATE
>>>>>
>>>>> On Feb 4, 2008 4:58 PM, Frederic Belleudy
> <fredericb@stripped> wrote:
>>>>>
>>>>>
>>>>>           
>>>>>> Hi there, I'm new with innodb and I'm not sure it's good to go
> with
>>>>>> innodb for my personnal goals.
>>>>>>
>>>>>> Ok, let's assume I 've a table and want to select the first 10
> rows from
>>>>>> that table but I want to be sure that no other scripts will
> select the
>>>>>> same rows I've previously got by the first script.
>>>>>>
>>>>>> How can I do that?
>>>>>>
>>>>>> my table contains one primary key. Let's say id is the column
> name.
>>>>>> So my first script is running and select the ids: 1, 2, 3 ....
> 10
>>>>>>
>>>>>> Then that script will play with the returned ids.
>>>>>> In the same time, I'm running a second script and do the same
> select.
>>>>>> But I don't want him to get the first 10 ids.
>>>>>>
>>>>>> The only thing I can think about is to lock WRITE my table. I
> taught
>>>>>> innodb was able to automatically lock the selected rows and not
> allowed any
>>>>>> other script to get the same rows until it's commited...
>>>>>>
>>>>>> Tks
>>>>>>
>>>>>>
>>>>>> --
>>>>>> MySQL General Mailing List
>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>             
>>>>>
>>>>>           
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>>
>>>>
>>>>
>>>>         
>>>       
>
>   
Thread
lock the row selected by a session and lock those rows for othersessionsFrederic Belleudy4 Feb
  • Re: lock the row selected by a session and lock those rows for other sessionsMichael Dykman4 Feb
    • Re: lock the row selected by a session and lock those rows for othersessionsFrederic Belleudy5 Feb
      • Re: lock the row selected by a session and lock those rows for other sessionsBaron Schwartz5 Feb
Re: lock the row selected by a session and lock those rows for other sessionsBaron Schwartz5 Feb
  • Re: lock the row selected by a session and lock those rows for othersessionsFrederic Belleudy5 Feb