Page 1 of 1

does MySQL support transactions for concurrency issues

Posted: Tue Mar 11, 2003 9:41 pm
by lazy_yogi
I have to write an online ticket allocation system for a small community-based show.

I'm just wondering if 2 people log on at almost the same time and try to purchase the same ticket, how would i deal with this ?

Is there a way to lock the database during the period or the first buying it so that the second has to wait ?

And if PostgreSQL is better for the job ... please let me know .... I have access to that also.

Any help would be great.
And example code would help alot also if anyone has any.

Cheers

Posted: Tue Mar 11, 2003 10:40 pm
by Stoker
Unless it is MySQL version 4 or newer it doesn't have transaction support, you can use LOCK TABLES before quering for what is available and then decrement it or whatever and UNLOCK afterwards (must be done within the same PHP script (lifetime of one script instance)..

Postgresql has transactions and could do it that way.. for this simple function it wont matter much which..

Posted: Wed Mar 12, 2003 6:30 am
by lazy_yogi
would you mind giving an example of code that uses lock and unlock please.

Cheers

Posted: Wed Mar 12, 2003 10:14 am
by Stoker
it would be exactlythe same way as for a transaction, do a lock first, then check and decrement, then unlock..

LOCK TABLES mytickets WRITE;

SELECT ticketcount FROM mytickets WHERE ticket_id=$id;

Fetch that row and see if there is any left, if there is, claim it and do a

UPDATE mytickets SET ticketcount = (ticketcount - 1) WHERE ticket_id=$id

and then if there is none left display some "sorry dude" info,

and finally unlock
UNLOCK TABLES;

Posted: Wed Mar 12, 2003 3:53 pm
by lazy_yogi
oh that was simple ..

thanx Stoker