[Last Call] Learn how to a build a cloud-first strategyRegister Now


mysql query Preventing simultaneous rows access

Posted on 2012-09-15
Medium Priority
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
  • 3
  • 2
LVL 25

Expert Comment

ID: 38402560
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.

Author Comment

ID: 38402811
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 111

Accepted Solution

Ray Paseur earned 1200 total points
ID: 38405181
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 38405219
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 111

Expert Comment

by:Ray Paseur
ID: 38405252
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.

Author Comment

ID: 38405798
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 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