CODEWITHSUNDEEP
phpmysql
user3787783
user3787783

Reputation: 7

How to implement locking mechanism for mysql table row to avoide race condition?

    <?php
    $time=getdate();
    $time['month'];
    $temp_time=$time['month']." ".$time['year'];
    $q="SELECT * FROM websiteviews ORDER BY id DESC LIMIT 1";
    $r=mysqli_query($dbc, $q);
    $r=mysqli_fetch_assoc($r);
    if($temp_time==$r['timespan']){
        $count=$r['view']+1;
        $q="UPDATE websiteviews SET view='".$count."' WHERE id='".$r['id']."'";
        mysqli_query($dbc, $q);
        echo mysqli_error($dbc);
    }
    ?>

Hi friends I am updating page views of my website every time webpage is loaded this piece of code is placed on the top of the page now my question is this mechanism can cause race condition i want to lock my row until it is updated please help with mysql query to lock particular row until it is updated

Upvotes: 0

Views: 393

Answers (3)

Sankalp Bhatt
Sankalp Bhatt

Reputation: 1204

You can use Transactions to solve the race condition, something like below:

START TRANSACTION;
SELECT view, id FROM websiteviews ORDER BY id DESC LIMIT 1 FOR UPDATE;

#id obtained from previous step
#view obtained from previous step
UPDATE websiteviews SET `view`=<view>+1 WHERE id= <id>;
COMMIT;

NOTE: Take note of the "FOR UPDATE" clause used with select statement.

Upvotes: 0

Robert
Robert

Reputation: 20286

I think you need use transaction locking but your tables need to use engine that supports transactions like InnoDB

You can read more on

Upvotes: 1

Pred
Pred

Reputation: 9042

You shoud not select then update the record you want to update.

UPDATE
  websiteviews
SET
  view = view+1
WHERE
  -- Assuming 'timespan' is a varchar column and the stored format is 'MM YYYY'
  timespan = DATE_FORMAT(NOW(), '%m %Y')

The above query updates the record in one step, so the race condition could be handled by the DBMS. Possibly you should change the WHERE condition to match to your requirements.

If the column view is nullable, then change the SET part to view = COALESCE(view, 0)+1

Storage engines

In MySQL the tables could be stored via different storage engines and all engines has different support of locking (if any).

Additional resource: About locking when InnoDB is the storage engine

Upvotes: 1

Related Questions