0

I'm looking for a solution for the following situation:

I have a tournaments table in mysql which has a maximum amount of sign ups. When a sign up occurs it's inserting the row in the tournament_players table.

My main problem is I don't know how to avoid the case when two or more sign ups occur at the same time and they exceeds the maximum amount of sign ups. I was researching about the transaction but I don't really understand how it works in practice. May I lock only the row or the whole table? AFAIK I know first needs InnoDB the second need MyIsam. Which is the best for my problem?

John Conde
  • 217,595
  • 99
  • 455
  • 496
Akos
  • 77
  • 7
  • My question is just theoretically yet. I'm wondering about a solution now when I have a counter col on the tournaments table which I try to increment when a player sign up. `UPDATE t SET count = count + 1 WHERE max > count AND id = tournament_id` If I have an affected row I save the sign up otherwise don't. What you think about it? – Akos Apr 03 '15 at 21:17
  • you should put the code of your comment back in the question (edit the question) – cmbarbu Apr 04 '15 at 00:43
  • @akos, Have you bee able to test this? – BK435 Apr 07 '15 at 17:30

1 Answers1

0
  1. Possible solution: How can I set a maximum number of rows in MySQL table?
  2. Have a function that checks the amount of rows in a table, if it exceeds what you want, dont run query and run error code instead.
Community
  • 1
  • 1
Siim Kallari
  • 851
  • 6
  • 17
  • I'm sorry but you may misunderstood me. Setting maximum number of rows doesn't fit my needs. I need to check if a user can sign up to a tournament without exceeding the maximum amount of sign ups and I'm afraid of a situation when two syncron request for a sign up beat each others and exceeds it. – Akos Apr 03 '15 at 21:16