Is Mysql (innodb) row level locking the solution?

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开发者_Go百科 to retrieve the reward.

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.