(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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > >>>> > >>>> > >>>> > >>>> > >>> > >>> > >>> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >> > >> > >> > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
