How can i lock the row of a table?

PHP
1

Hi ,

Let’s assume I 've a table and want to select the first 5 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 … 5

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 5 ids.

How can i lock the row table in cakephp ?
please help me thanks in advance…

2

Well i’m assuming r937 will be along in a minute to answer it from the MySQL-only perspective, but from my perspective:

Unless you’re concerned about microsecond collision, add a field to the table called ‘lock’. when you retrieve rows, set lock to 1. when you’re done with them, set lock to 0. Dont pull rows with lock = 1.

(Far as i know, MySQL doesnt do individual-row locking. r937 will be able to say better than I, though.)

3

I’d have a look at SELECT … FOR UPDATE.
In combination with a lock field (as suggested by StarLion) and transactions you could get what you want.

4

i’ve got no experience with locking, sorry

i am concerned, though, with a couple of things…

“select the first 5 rows” has to be based on some column, and using an id (presumably auto_increment) isn’t very robust

“play with the returned ids” is pretty nebulous

what exactly are you doing?

5

Yes but the problem with that is that its only valid while the script is running. If you select 5 records to output to a edit page for instance, then the lock is lost because as soon as the page is output… well i don’t need to explain this to you.

Fine if its all done in one script execution.