Transazioni ed accessi concorrenti

[Alhazred]

Ho 2 tabelle per gestire degli acquisti, la loro struttura, a cui ho lasciato solo i campi utili a spiegare cosa mi serve, è la seguente.

PRODOTTI
-------------------------------------------
| id | nome | quantita | prezzo | venduto |
-------------------------------------------
il campo quantita, quando un prodotto è disponibile, sarà sempre 1, non sono previste quantità superiori
il campo venduto vale 0 finché non si effettua un acquisto, poi diventa 1

ACQUISTI
-------------------------------------------------------
| id | prodotto | acquirente | prezzo | data_acquisto |
-------------------------------------------------------
il campo prodotto referenzia l'id del prodotto

Il problema è questo: la gestione di acquisti concorrenti.

Ho già letto la pillola riguardo a questo aspetto, ma mi ha lasciato dei dubbi avendo letto anche altre cose in giro.

Ho intenzione di eseguire un acquisto faccio così (lo scrivo con mysql perché mi viene più veloce e lo possono capire tutti, per il progetto uso CodeIgniter e ActiveRecords e magari qualcuno non lo conosce e non capisce cosa succede)

mysql_query('BEGIN'); //inizio una transazione
$query = "SELECT id FROM prodotto WHERE id=id_del_prodotto AND quantita=1 AND venduto=0";
$result = mysql_query($query);
if ( mysql_num_rows($result) == 1 ) //il prodotto è stato trovato
{
    //update del prodotto acquistato
    $query = "UPDATE prodotti SET quantita=0, venduto=1 WHERE id=id_del_prodotto";
    $result = mysql_query($query);
    if ( mysql_affected_rows() == 1 ) //l'update è avvenuto
    {
        //inserisco l'acquisto nella tabella acquisti
        $query = "INSERT INTO acquisti ....";
        $result = mysql_query($query);
        if ( mysql_affected_rows() == 1 )
        {
            mysql_query('COMMIT');
            return TRUE;
        }
        else //INSERTfallito
        {
            mysql_query('ROLLBACK');
            return FALSE;
        }
    }
    else //UPDATE fallito
    {
        mysql_query('ROLLBACK');
        return FALSE;
    }
}
else //il prodotto non è stato trovato o la quantità non è 1, dunque nel frattempo l'hanno acquistato
{
    mysql_query('ROLLBACK');
    return FALSE;
} 

Bene, inizia una transazione, esegue la SELECT che va a buon fine e si va avanti.
Prima del COMMIT o di un ROLLBACK arriva un altro utente che vuole comprare lo stesso prodotto, quindi anche lui effettua la SELECT.
A questo secondo utente la SELECT andrà a buon fine e andrà avanti anche lui o si attiverà una LOCK sulla riga e quindi la SELECT del secondo resterà in attesa di essere eseguita fino a che la prima transazione non sarà terminata? In questo secondo caso non ci sarebbe alcun problema, ma non so la risposta.

Potrei anche mettere a 1 il campo "venduto" subito dopo la SELECT, ma se la seconda transazione avviene prima che lo faccio comunque ho un problema.
Ad ogni modo anche così facendo, la modifica al campo "venduto" sarebbe all'interno della transazione e il secondo utente non se ne accorgerebbe dato che non è realmente impostato sul DB.

Insomma, mi basta usare una transazione come specificato sopra o devo preoccuparmi di altro?

[cris.calleo]

Il venduto, io lo metteri solo dopo che la transazione si concretizza.

Buttandola giu, io darei a tutti i visitatori di poter mettere nel carrello/comprare l'item ed al primo acquisto confermato cambio il valore a "venduto".
Se altri visitatori hanno l'articolo nel carrelli e nel frattempo qualcuno ha già concluso l'ordine viene mostrato il messaggio di indisponibilità:

"Mi dispiace ma l'articolo é stati venduto X minuti fa, la prossima volta sbrigati!!"

E se ci sta, permetti all'utente di salvare il tutto come "Ordine Futuro" e gli arriva na mail quando il prodotto sará di nuovo disponibile.

[Alhazred]

Ho trovato che usando SELECT ... FOR UPDATE posso risolvere il problema degli accessi concorrenti, la entry selezionata viene bloccata per tutte le transazioni diverse da quella che esegue la SELECT, mettendo la select come prima cosa sono a posto.

La tua visione della cosa comunque non è male, vedrò di implementarla.

[cris.calleo]

SLECT FOR UPDATE bloccherebbe la transazione fino a quando non si concretizza.
Presumo se un utente ha messo nel carrello l'articolo lo congeli fino al checkout, o sbaglio?
E se alla fine non lo compra e nel frattempo hai un secondo visitante che é interessato?

[Alhazred]

No no, la SELECT FOR UPDATE non la faccio quando l'utente mette il prodotto nel carrello, ma quando conferma l'effettivo acquisto, la farò nella transazione di pagamento per intenderci.