mysql query Preventing simultaneous rows access

hello i have a sql server database and it's contain a table and inside this table i receives from a mobile operator in my country to run a short code service .. and i received sms from mobile user and it will be stored in this table for this reason every couple of second new sms will be stored in this table

and now i want to make a php application where i can extract this sms and get the content of the sms sended from mobile user and to filter it if he send letter A it will be subscribed to serices A and UA to unsubscribed from service A so  i should make a php script to work all time to get new sms and put a flag read before not reading it again... but :S how i can do it ?

because when i run or execute php script to connect to sql server and fetch row in this table each 1 min for example  have simultaneous rows access how i can solve it ??

i have read two much in lock commit rollback but i don't know the concept of it

so if we can help me  to find the best solution to filter sms sended from mobile user where it's stored in specific table and make a flag read to not read it again when i run the cron job .. but the problem will be if the first executing is not finish and still work and the another one work :D

the problem script1.php fetch 100 rows at row 30 will read and update the flag to read
but in this time. the script1.php is already running and he try 2 execute the same row so error occurs

i try two run the php script for a couple of minute using windows scheduling task because i don\t know how to install cron job on IIS

i work in win server 2003 IIS  PHP MYSQL SQL
Who is Participating?
Ray PaseurCommented:
Here is a "rough-cut" example of how to lock and unlock tables.  I believe if you use INNODB tables with MySQL you can set locking at the row level.

function addSignal($state_code)
    $sql = "LOCK TABLES statelog WRITE";
    $res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error());

    $sql = "SELECT sig FROM statelog WHERE id = '$state_code' LIMIT 1";
    $res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error());
    $row = mysql_fetch_assoc($res);
    $sig = $row['sig'] + 1;
    $sql = "UPDATE statelog SET sig = $sig WHERE id = '$state_code' LIMIT 1";
    $res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error());

    $sql = "UNLOCK TABLES";
    $res = mysql_query($sql) or die("FAIL: $sql <br/>" . mysql_error());

Open in new window

HTH, ~Ray
You mention MySQL and SQL Server in your question ... which is it?  Have a read of this article - although it is MySQL, the message applies to most DBMS's.
The best piece of advise in it I think is - do not use the database for a queue, use a purpose built system.
AFIF JABADOAuthor Commented:
thank for your , by the way i don't know anything about lock in mysql ...
i want a simple php script where i can select some item from database for example and lock all and prevent other users to read it !!! after do my job i will change the status of this msg...

1- sms list stored in database
2- read the list take each 100 sms proccess this msg ! thank make sure the script of proccessing work as cronjob for this reason when we select the 100 sms from table we should lock it where we prevent another job to read the same rows.

so if we can give me an implementation of locking read .. and i update... than i change the sms status to done when i proccess the msg the status will be processing.. and like this

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AFIF JABADOAuthor Commented:
this function lock the table .. i have a php script connect to sql server and fetch row the table fethed is like a queue each time we have new rows inside it for this reason i use a windows task scheduler or cron job to run the  same script several time each 2 min for example . and i want a solution to prevent simultaneous rows access ..and when he try to access to a row is blocked he try to jump to next one ...

or if we have another solution i don\t have any problem..
Ray PaseurCommented:
Yes, it locks the table because it is going to SELECT a row, then UPDATE the row with a new value.  But reading this...
when he try to access to a row is blocked he try to jump to next one
... it leads me to believe that you have a bigger systems-analysis issue here.  I do not know of a data base design that would fit that description without a significant amount of additional programming.
AFIF JABADOAuthor Commented:
i have a table name sms_in in a database . where he is contains the following attribute.
sms_inid(pk) \ sms_in_mobilenumber \ status \ sms_in_content \ sms_in_readflag \ sms_in_timestamp

this table is always update because i receive from mobile operator a lot of SMS to the specific short code !!

my next steps is to proccessing all sms and read one by one .. for example when i run the smsprocessing.php he will select all unradflag an do a certain job....

during the processing i received new sms from mobile operator for this reason the script smsprocessing.php should run several time .. and this is my problem i am afraid for simultaneous rows access or reading the sms two times

because this system receive sms from mobile user and read the content of sms.. if it\'s letter A , automaticly the script add the mobile number of this user to another table , and the mobile users will be subscribed to service A..

this my first time i work in this type of database and i don\t know anything about looking ut now i try to find the good and better solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.