SELECT...FOR UPDATE with prepared statement is not working

Question

I am trying to create a fb-like-button function(not facebook api). There are two buttons on my website: like and dislike. Database will save how many likes and dislike in total.

Here is the db table:

id |post_id |like_count
40 |     20 |         0

code for when user click like button:

$id = 40;
$conn->autocommit(FALSE);
$conn->query("BEGIN;");

//lock the row to prevent race condition
$sql = "SELECT like_count FROM post_like WHERE id = ? LIMIT 1 FOR UPDATE";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

//update the table
$sql = "UPDATE post_like SET like_count = like_count + 1 WHERE id = ? LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

$conn->commit();
$conn->autocommit(TRUE);
$conn->close();
//when success, display unlike button to user throught AJAX

and code for when user click unlike button:

$id = 40;
$conn->autocommit(FALSE);
$conn->query("BEGIN;");

//lock the row to prevent race condition
$sql = "SELECT like_count FROM post_like WHERE id = ? LIMIT 1 FOR UPDATE";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

//update the table
$sql = "UPDATE post_like SET like_count = like_count - 1 WHERE id = ? LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();

$conn->commit();
$conn->autocommit(TRUE);
$conn->close();
//when success, display like button to user throught AJAX

here is the problem...
number of like_count starts at 0.
Theoretically, if there is only one person click the button, like_count will not exceed 1 or less than 0.
(click like button->->lock row->like_count+1->release row->display unlike button)
(click unlike button->lock row->like_count-1->release row->display like button)
I get this work right when I click the button slowly, however, when I keep on clicking the button quickly, number of like_count can exceed 2, and sometimes it can be a negative number.
I have no clue what I have done wrong. Please help!

Let me ask you a question. What's wrong with race condition here? — Your Common Sense, Aug 29 '13 at 08:42

score 0 · Answer 1 · answered Aug 29 '13 at 08:43

0

in your update query put

$stmt->execute();

below

$stmt->bind_param('i', $id);

answered Aug 29 '13 at 08:43

chirag ode

950
7
15

yes, execute(); is below bind_param in the code im working with, just a mistake posting the code here, this is my first post, thanks for pointing that out – user2728266 Aug 29 '13 at 08:47

score 0 · Answer 2 · answered Aug 29 '13 at 08:47

0

The code to handle all the buttons.

$amount = (isset($_POST['like'])) ? 1 : -1;
$sql    = "UPDATE post_like SET like_count = like_count + ? WHERE post_id = ?";
$stmt   = $conn->prepare($sql);
$stmt->execute(array($amount,$_POST['id']));

note that you don't need an id column in this table. post_id is all right

answered Aug 29 '13 at 08:47

Your Common Sense

156,878
40
214
345

this code is not intended to solve whatever your problem, mind you. it is the right code to handle your button on the PHP side. While regarding your particular problem, you have to find out first, what is the problem you have – Your Common Sense Aug 29 '13 at 09:18

score 0 · Answer 3 · edited May 01 '18 at 13:30

0

Change:

$conn->autocommit(FALSE);

To:

$conn->autocommit(TRUE);

update and insert query doesn't work with $conn->autocommit(FALSE);

edited May 01 '18 at 13:30

slfan

8,950
115
65
78

answered May 01 '18 at 13:10

Tanveer Khan

21
4

SELECT...FOR UPDATE with prepared statement is not working

3 Answers3