PHP/SQL script handling question

saljutin

lets say we have 2 tables in our database
table1 (id/number1/number2/randomnumber)
table2 (id/sum)
php scrip does:
+query table1 and sort it by randomnumber
in while sentence we have
+calculated=number1*number2
+insert into table2 sum VALUES calculated
+deletes the line in table1 which was used in part of proccess
continue, stops when while=false (when table1 is empty)

now the question: lets say this script runs for 10 seconds and user make it run by opening some .php file in his browser.
1st user opens page, script starts; after 2 seconds 1 more user opens page; after 2 more seconds 1 more user opens page...
So how is this sql/while thing working...is sql smart enough so you dont get error or duplicate entries (or even lost some entries)?
table1 rows (before running) = table2 rows (after running), is this always true, no matter the fact plenty users come and open .php file?
Thank you for all answers

aksival

If I'm not mistaken, once User 1 opens the database table the affected row(s) are "locked" until the query is finished executing. User 2's query would not affect the locked rows until User 1's query has finished executing.

At least I think that's how it works...someone tell me if I'm wrong. 🙂

Weedpacket

Depends on the DBMS being used and how well it's written. But as aksival says, any worthwhile DBMS should be able to handle concurrency issues like that. Consult the appropriate documentation for details.

Sxooter

Yes, you've got a concurrency issue. how do you want this handled? Is table2 supposed to be empty at the beginning each time? Tell us what you're trying to accomplish and I might be able to make a suggestion on how to do it.

saljutin

I use PHP+mySQL. so script is doing this...
read thing from table1 and depend on result it changes some values in this line OR changes values in some other line and delete line where it reads from. the only problem is .... how can I ensure that some line wont be used at the same time in same php script (runned by 2 users), because then its error or double entry...

saljutin

double post - delete...sorry

Sxooter

So, table2 does not get emptied out then? What you need is a column in table1 that is used to lock the row from an application perspective. i.e. a column called status that you put a 1 or something in when you're working on it.

So, your script does something like:

select * from table1 where status <>1;

processes those rows, tosses them into table2, then deletes them by id number from table1.

saljutin

so if I get this right, I should add another column (status) with
1 - being processed
2 - ready for processing
but in script that would lead to 2 more queries, 1st at the beginning of while and 2nd at the end. like this:
while () {
if status = 1 {
break
}else{
UPDATE row SET status = 1
other stuff here
DELETE row OR UPDATE row SET status = 0
}
}

that would be great solution yes...but it would also enlarge amount of sql queries...

Sxooter

The idea is that you're script "checks out" the rows it's going to process first.

Assuming that 0 means unprocessed, and we want to be able to process from more than one script, you could do something like:

$int=random(2,2000000000); # Not really fool proof, but close
update table1 set status=$int where status=0
select * from table1 where status=$int
while... process here, inserting to table2
delete from table where status=$int # or set it to 1 to show it's done if you wanna keep the rows for some reason

saljutin

dont think this random thingy is right solution. OK I will reveal my plans now. I have table1 which has column with timestamp, when job is finnished.
select * from table1 where time<=time()
while ...
if status=0
update status=1
RUN the whole script, at the end delete row or change time(script job) & status
else (meaning status=1, someone else run that script)
empty (meaning continue to next line in selected array)
} end of while

This should work I think, but problem is I gain 1 more sql query. so noone login for 1hour, then they come and there are 1000 unprocessed lines in table (time<=time()), that means +1000 sql queries (because of update status=1)
how is this going to affect stability, resources & time needed?