does MySQL support transactions for concurrency issues

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

does MySQL support transactions for concurrency issues

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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..
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

would you mind giving an example of code that uses lock and unlock please.

Cheers
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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;
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

oh that was simple ..

thanx Stoker
Post Reply