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 <[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]
