sql - Atomically establish relation between records -

i'm developing e-commerce website delivers unique digital products (e.g. 40-digit codes). these digital products stored rows in stock table. when order placed, one unique digital product should assigned order.

currently, i'm selecting oldest product (in first-in first-out manner), doing business logic (sending e-mail customer, etc.) , deleting record stock table. problem operation not atomic. e.g. if 2 orders placed @ same time, same code selected, e-mailed customer, , deleted (though second delete fail, ofcourse). leaves database in inconsistent state (from business perspective), because same unique digital product assigned 2 orders.

how establish link between digital product table stock , order table order in consistent/atomic fashion?

ps. i'm using mysql (myisam), i'm not fond of lack of referential integrity. plan on moving either innodb or postgresql in near future. therefore, i'd prefer technology-independent answer.

add column stock table. call allocated or somesuch. values 0 (not allocated) or 1 (is allocated). 0 default. once read row stock add customer's order have primary key, i'd assume. you

update stock set allocated = 1 stock.primarykey = <the value read> , stock.allocated = 0; 

this 1 statement is atomic definition, unless have pathologic isolation level set.

if fails else has called "dibs" on stock item. loop "read oldest stock item" step. of course want read items where allocated = 0. repeat needed, or run out of stock items.

if process fails catestrophically @ subsequent step may end orphaned stock items i.e. have allocated = 1 no email has ever been sent.

you better off having transaction around these steps since expect them succeed or rollback.