Is Mysql (innodb) row level locking the solution?

Question

Hello,

Recently ran into a problem after putting my codeiginter application into a clustered environment. Users are able to get site rewards at point levels, after they click submit to retrieve the reward I have code that grabs the current point level of the reward is at and save that in a table with the user_id and what point level they got the reward at. it is suppose to increment that reward by 1 point level after the user has retrieved the reward. I have ran into a problem where 2 users might click submit at the same time and in the records table it has that both users got the reward at the same point level which shouldn't happen because it is suppose to increment by 1 point after each submit. would row level locking on the reward row that is being incrementing stop this issue ? and how do i implement it?

If you need anymore information or clarification let me know.

• Users Table
  id
  username
  [...]

• Rewards Table
  id
  name
  points

• Records Table
  id
  users_id
  rewards_id
  points

The points in the records table is suppose to be the amount of points the reward was at when the user submitted to retrieve the reward.

Answer 1

No. Row-level vs table-level locking is primarily a performance issue.

What you're looking for is transactions. Start the transaction, increase the point level, add the reward, commit. Either the whole transaction completes and both the reward and the point-increase are done at once, or it fails and neither happens.