concurrency - MySQL INSERT SELECT WHERE race condition -

i'm working on ticketing system users escrow large amount of tickets @ once (basically tickets not out of stock) before claiming them. these tickets shown user , can select whichever ticket want claim.

this escrow system introduce race conditions if 2 users try escrow same tickets @ same time , there aren't enough tickets, in:

tickets left: 1

user hits page, checks number of tickets left. 1 ticket left user b hits page, checks number of tickets left. 1 ticket left

since both have ticket left both escrow ticket, making tickets left -1.

i'd avoid locking if @ possible , wondering if statement subqueries like

insert ticket_escrows (`ticket`,`count`)  select ticket,tickets_per_escrow tickets tickets.total > (     coalesce(         select sum(ticket_escrows.count) ticket_escrows          ticket_escrows.ticket = tickets.id         , ticket_escrows.valid = 1     ,0)     +     coalesce(         select sum(ticket_claims.count)          ticket_claims         ticket_claims.ticket = tickets.id     ,0) ) 

will atomic , allow me prevent race conditions without locking.

specifically i'm wondering if above query prevent following happening:

max tickets: 50 claimed/escrowed tickets: 49 t1: start tx -> sums ticket escrows --> 40 t2: start tx -> sums ticket escrows --> 40 t1: sums ticket claims --> 9 t2: sums ticket claims --> 9 t1: inserts new escrow since there 1 ticket left --> 0 tickets left t2: inserts new escrow since there 1 ticket left --> -1 tickets left 

i'm using innodb.

to answer question "if statement subqueries ... atomic": in case, yes.

it atomic when enclosed in transaction. since state you're using innodb, query subqueries sql statement , such executed in transaction. quoting documentation:

in innodb, user activity occurs inside transaction. if autocommit mode enabled, each sql statement forms single transaction on own.

...if statement returns error, commit or rollback behavior depends on error.

also, isolations levels matter.

in terms of sql:1992 transaction isolation levels, default innodb level repeatable read

repeatable read may not enough you, depending on logic of program. prevents transactions writing data read transaction until reading transaction completes, phantom reads possible, however. check set transaction way how change isolation level.

---

to answer second question "if above query prevent following happening ...": in transaction serializable isolation level cannot happen. believe default level should safe in case (supposing tickets.total doesn't change), i'd prefer having confirmed someone.