[RESOLVED] Locking a row from being READ

[eyeRmonkey]

I need to temporarily lock a row from being read by any other queries. I need to do this because I need to get the next row marked as "TODO", then change it to "IN PROCESSING". I don't want one of the other instances of my script to also grab it before I have a chance to mark it as "IN PROCESSING".

Current scenario (not desired): (in order of execution)
SCRIPT 1: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 2: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 1: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on
SCRIPT 2: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on, but now two scripts have a handle on the same record


Desired scenario: (in order of execution)
SCRIPT 1: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 2: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 456 because 123 gets ignored somehow
SCRIPT 1: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on (123)
SCRIPT 2: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on (456)

I would LIKE to be able to do this all in one query, but I will settle for multiple queries if that's what must be done.

I know MySQL has the SELECT ... FOR UPDATE command, but I can't figure out how to use and the idea of messing with auto commit stuff scares me. I don't want to turn off auto commit and have to change all my other queries just to make the manually commit.

I'm using PHP and MySQL 5.

Any help at ALL would be greatly appreciated.

[szlamany]

Locking is always a bad idea.

Think about how SQL processes the rows and how you can leverage the "order-of-change" to your advantage.

In your current scenario - two scripts grab the same primary key of 123.

But then you simply update the row based on that primary key - and of course those two scripts can update the same row

But in reality in SQL (and I hope mySql does this the exact same way) - updates occur in order. One of those two scripts will do the update first.

That means that if you change the update statement to:

UPDATE artists SET status='in processing' WHERE primary_key = 123 and status='todo'

Then only one script will ever get to do this update. The second script - when attempting to do the update will find no row with a prikey of 123 and a status that is still 'todo'.

Here's how we leverage this concept in T-SQL in a stored procedure.

RedoB:
	Set @DataValue=(select DataValue from Control_T
		where FiscalYr=@FiscalYr and Item='LAST BATCH')
	Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
	
	Update Control_T
	Set DataValue=@NewDataValue,Tdate=@Tdate
	Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
	
	If @@RowCount<>1 Goto RedoB

[eyeRmonkey]

Thanks for your reply szlamany! I was hoping you would be able to provide some input on this.

What you said is true, but I forgot to mention one thing. After the update occurs, I do 60 seconds worth of processing on the result I got. So when both scripts are processing the same album, it's a big waste of time. Let me update the scenarios:

Current scenario (not desired): (in order of execution)
SCRIPT 1: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 2: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 1: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on
SCRIPT 2: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on, but now two scripts have a handle on the same record
Both scripts spend 30-60 seconds of processing time based on the key it got out of the database. There's not reason to have both scripts be doing the same work!

Desired scenario: (in order of execution)
SCRIPT 1: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 2: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 456 because 123 gets ignored somehow
SCRIPT 1: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on (123)
SCRIPT 2: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on (456)
From here, SCRIPT 1 would process 123 and SCRIPT 2 would process 456.

Note: None of the "processing" I'm mentioning has anything to do with the database. The database is there just for me to find the right primary_key to proceed with my other work.

EDIT: At the moment, I'm just going to start using mysqli() instead if mysql() in PHP. The former allows me to do multi-queries so I can do the SELECT and the UPDATE "at once" if that makes sense.

[szlamany]

Note: None of the "processing" I'm mentioning has anything to do with the database. The database is there just for me to find the right primary_key to proceed with my other work.

But you are going to have to do the UPDATE to "in processing" before spending the time doing the 60 seconds worth of work.

And that UPDATE - on the client side - will have to see if a ROW WAS UPDATED. If not - it's going to have to get the next available pri eky.

Are you saying you get the ID and then do the 60 seconds and then do the update to in processing??

SCRIPT 1: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 2: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 456 because 123 gets ignored somehow

SCRIPT 1: UPDATE artists SET status='in processing' WHERE primary_key = 123 and status='todo' updates the record it just got a handle on (123)
If no rec updated - go back to SCRIPT 1:SELECT...

SCRIPT 2: UPDATE artists SET status='in processing' WHERE primary_key = 123 and status='todo' Find no record to update - so try again - gets 456
If no rec updated - go back to SCRIPT 2:SELECT...

From here, SCRIPT 1 would process 123 and SCRIPT 2 would process 456.

[eyeRmonkey]

The 60 seconds of processing does happen AFTER I update the row and mark it as "in processing".

The scenario you mentioned would, of course, be what I WANT to happen, but since these scripts are running asynchronously, the series of events that I listed is still a possibility.

[szlamany]

No it is not possible.

Only one UPDATE can occur with the STATUS='todo'.

After the first UPDATE happens - the status no longer = 'todo' so the second update will not occur. In MS SQL T-SQL @@RowCount tells us no rows were updated - our clue to the fact that the update didn't match successully.

Not sure what in mySql you would use to accomplish the same thing.

What makes you think that your async situation would allow anything other than this to happen??

[eyeRmonkey]

Simplicity was the key!

That's it.

Here's what I was thinking before you mentioned checking the affected rows: In the "undesirable scenario" I wouldn't have checked the number of rows affected by the update and both scripts would have gone on with the 60 seconds of processing.

You're very correct, though. If I simply make sure that the update affected one row, I'm fine. If it didn't affect one row, then I will just keep doing queries until I get a row that I can successfully update.

Perfect! I haven't coded it yet, but I understand the concept.

Thanks so much szlamany. RobDog and Penagate told me you were the SQL master. That seems to be the case.

[szlamany]

You are very welcome - always happy to help!

[eyeRmonkey]

Alright. I realized I have another problem. If I need to do this multiple rows, the idea breaks down. Example:

SELECT * FROM artists WHERE status='todo' LIMIT 100;
UPDATE artists SET status='in_processing' WHERE id IN (SELECT id FROM artists WHERE status='todo' LIMIT 100);

I can't just check to make sure that the number of updates rows was equal to the number of rows selected. Well, I can check for it, but it is costly to just keep re-performing the query until they are equal.

Does that make sense?

In PHP, I can't easily do a multi-query, so I have to do those queries separately and before I do the second one, there is a chance that one of the other asynchronous copies of this script could SELECT the same 100 for processing.

[szlamany]

Well...

You can grab a range - check the range in the same WHERE clause.

In MS SQL I would write a SPROC to load a temp table with the id values - checking each one.

Do you have stored procedures available to you?

[eyeRmonkey]

I do have temp tables available to me. It just seems like a hassle to me, but I suppose it's worth it.

Could you show me what your procedure would look like? I can translate it to MySQL afterwards.

Thanks!

[szlamany]

Do you have stored procedures??

Are the primary keys in order? That's probably the most important question. Does each client want 100 records? Or are the numbers more random then that?

[eyeRmonkey]

The primary keys aren't in any sort of order, but I don't see that being important. The primary keys are also strings, not numbers.

The numbers are more random than that. Sometimes I'll need 100, sometimes I'll need 10 or 1000.

Yes I have stored procedures available.

[szlamany]

Then you create a stored procedure that takes as a parameter the number of id's that are to be returned.

And you look at the code I pasted in post #2 and work that into the stored procedure.

In addition to the UPDATE - if the update does occur - you put the value into a temp table.

So basically you loop for available id's - in an outer loop of the number of id's that are to be returned.

And each one that's "gotten" - by the where clause taking an UPDATE row - is placed into the temp table.

Then when done - when you have the number of rows asked for - you SELECT IDNUMBER FROM #TEMPTABLE and return that recordset to the client.

[eyeRmonkey]

I'll be honest, I didn't understand your reply that well.

I gave in and figured out how to do multi-queries in PHP and came up with this:

DROP TABLE IF EXISTS artists_temp;
CREATE TEMPORARY TABLE artists_temp SELECT sql_id FROM artists WHERE status=0 LIMIT 20;
SELECT * FROM artists_temp;
UPDATE artists SET status=1 WHERE sql_id IN (SELECT sql_id FROM artists_temp);

It works fine. I can't foresee any problems with running this asynchronously unless MySQL decides to squeeze other queries inbetween these, but I don't think that is a possibility. As long as these all run in sequence, I'll be fine.

Thanks again for your help.