1

I am using mysql, php. table user_meetup id, user_id, meetup_id with unique on (user_id, meetup_id) if my meetup is limited by places(10 places) 9 users already marked rsvp and if 2 users doing RSVP at the same time (i just have consider a case of concurrency)

A -> select count(id) from user_meetup -> result : 9 hence go ahead
B -> select count(id) from user_meetup -> result : 9 hence go ahead
A -> insert ......   
B -> insert ......

now both of them will get place and my count became 11, my solution was to add one more table user_meetup_count id, meetup_id, user_count ( count set to 0 as default value and record is created as soon as meetup is created) now if use update query like update user_meetup_count set user_count = user_count + 1 where user_count < 10 and if I write in user_meetup table based on number of rows updated by above query if it returns 0 mean its full and if it returns 1 I got the place

will this work in case 2 user try at same time ? is my approach to solve concurrency problem right ? is there a better way ? what are the tools to testing this type of situations ?

Akshay Ransing
  • 105
  • 7

1 Answers1

1

Use lock tables before counting. And unlock it after inserting.

Or you could use GET_LOCK then RELEASE_LOCK. With this you do not need to lock all entry table.

Or explore theme about gap locking for innodb tables. With this you need to use transactions.

And you could use jMeter for testing your queries.

sectus
  • 15,605
  • 5
  • 55
  • 97
  • no I dont want to lock entire table for this operation. the tables I mentioned are not actual tables so users one action will approximately 100 rows to get inserted and those all 100 rows need to be checked before insert. some grouping conditions are there hence even counting table might get update 20+ different rows for users one action – Akshay Ransing Jun 26 '13 at 13:32
  • Yes, I actually I have been through these links before asking question except jmeter thanks for that. – Akshay Ransing Jun 27 '13 at 11:20
  • I would like if you can answer on my 1st question about my approach, I know at the end I might have to go back to row level locking in innoDB, I just wished if I could solve it without writing any locking and handling failure to get lock and all these conditions those I have to deal with db locks, I am actually from java background so writing synchronised block was easy :) – Akshay Ransing Jun 27 '13 at 11:25
  • @AkshayRansing, yes, it will work. But when you are doing update mysql also do locking. – sectus Jun 27 '13 at 14:16
  • yes my problem was i am can't lock entire table while I do inserts and to do row level works on update queries. and in my case i dont event have unique constraint on table where I am inserting. I only have limit on rows for some entities. Hence I have to come up with solution like this. – Akshay Ransing Jun 27 '13 at 18:48
  • @AkshayRansing, with GET_LOCK you do not have to lock whole table. – sectus Jun 27 '13 at 22:45
  • Even though I might not use it, but I liked GET_LOCK, I can solve my problem if I use it with following sequence lock, count, if (ctn < maxCtn) { insert }, release. I think in my solution I dont have to use external lock and hence my code will be less and I dont have to handle unable to get lock condition. – Akshay Ransing Jun 28 '13 at 06:02
  • http://www.xaprb.com/blog/2006/07/26/how-to-coordinate-distributed-work-with-mysqls-get_lock/ gave nice idea about GET_LOCK – Akshay Ransing Jun 28 '13 at 06:04
  • Yeah, it simple. I am glad. – sectus Jun 28 '13 at 06:26