mysql query Preventing simultaneous rows access

Posted on 2012-09-15
Last Modified: 2012-10-17
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
Question by:afifosh
    LVL 25

    Expert Comment

    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.
    LVL 1

    Author Comment

    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

    LVL 107

    Accepted Solution

    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
    LVL 1

    Author Comment

    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..
    LVL 107

    Expert Comment

    by:Ray Paseur
    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.
    LVL 1

    Author Comment

    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

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now