0

I faced interesting problem in very simple case. I have posts and users table in MySQL database. Posts can be liked by users. So there is separate table called likes that has columns: user_id, post_id.

When user taps on like button in app, request to php script is executed. Script is checking if there is row in a table where post_id and user_id matches information from request. user can’t has more than 1 like to a post, and if I press like on already liked post, previous like should disappear

I use 2 queries now: 1) check like to exist in table 2) If record does not exist - I am adding it, and if exists, I delete it.

Now I see that there are double likes for same post from same user in my table. It seems that two requests from single user could be executed almost instantly.

How can I optimize it - so it would be impossible to add two likes to one post from user? I think that I need to run single query instead but what should it be?

Improve this question
3
  • 1
    you need to place your query samples, if you need a better answer. It will help users to get well idea about your problem. Commented Sep 3, 2018 at 5:22
  • I think you need to synchronize the call to your PHP script Commented Sep 3, 2018 at 5:27
  • look at LOCK TABLES - to avoid simultaneous updates. LOCK the table before SELECT and UPDATE - once done; UNLOCK TABLES Commented Sep 3, 2018 at 5:36

3 Answers 3

Reset to default
3

Obviously your insert/update part is fired multiple times. You should investigate why this is happening and then prevent this if possible.

Because of multiple calls, the DBMS is being asked multiple times whether the entry exists. The answer is no for all calls. Then multiple inserts are fired and you get duplicates. So on the SQL side there are two things getting wrong:

  1. Why is it even possible to insert multiple records for the same post and user? A primary or unique key should be provided to make this impossible.

I consider this obligatory for your datamodel.

  1. The sequence of actions is wrong. A possible solution would be an insert first (one would succeed, the others would fail - the above mentioned key provided of course!), and on failure an update would be issued. MySQL, however, can even do this in one step:

with ON DUPLICATE KEY:

insert into likes (post_id, user_id, like_date)
 values (@post_id, @user_id, current_date)
 on duplicate key update like_date = current_date;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Use InnoDB, then

START TRANSACTION;
SELECT ... FOR UPDATE;
if it does not exist
    INSERT ...;
else
    DELETE ...
COMMIT;

By using transactional semantics, you avoid any issues with multiple connections stepping on each other. I think I have even avoided any chance of deadlock.

Improve this answer

Comments

-1

just use a flag (column with value 0 or 1) , and update the value without deleting the entire row.

your steps should be as follow:

  1. check for row existence.
  2. if exists update the column (like - 1, unlike - 0)

that's it.

in templates check for 0 or 1 for unlike and like

Improve this answer

2 Comments

There's no need to 'check' anything.
the checking is for display like/unlike icon in front end template

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.