Mysql, PHP, contatori, lock table & locks in generale

[brancomat]

Sto cercando di creare un contatore mysql, su un server con PHP, che in sostanza ritorna un numero+1 ogni volta che viene chiamato.

Come fosse un campo autoincrementante, solo che lo voglio manipolare io.
A macro-blocchi avevo pensato

lock table xxx WRITE;
leggi il contatore, incrementalo, scrivilo
unlock tables;

Fin qui bene, non ho problemi di prestazioni in quanto il contatore verrà richiamato 10 o 20 volte al giorno da 5 o 6 client, quindi lock table mi va benissimo... MA
quando c'è un LOCK TABLE in corso gli altri client rimangono "congelati", e questo è bene.
Purtroppo non so se esiste un modo per far andare in timeout dopo un po' la connessione, ho bisogno che se dopo 3 o 5 secondi la richiesta non è andata a buon fine il client "si svegli".

Allora ho provato coi LOCK di mysql, e questo funziona bene, anche perchè c'è la funzione di test rapido che mi consente di "sbloccare" subito un client se il lock è già attivo.
Se risulta libero => provo ad acquisirlo. In pratica spero di avere fortuna, nel caso peggiore aspetto il timeout che è impostabile

$query='SELECT IS_FREE_LOCK("lock_zconta");';
$result = mysql_query($query, $db) or die('-6');
$row=mysql_fetch_row($result);
if ($row[0]=='0') die('-7');

$query='SELECT GET_LOCK("lock_zconta",5);';
$result = mysql_query($query, $db) or die('-8');
$row=mysql_fetch_row($result);
if ($row[0]=='0') die('-9');

$query = 'SELECT coalesce(max(ultimoid),100000) as massimo from '.$fulltabella;
$result = mysql_query($query, $db) or die(mysql_error('-10'));

$row = mysql_fetch_assoc($result);
$massimo  = $row['massimo']+1;
echo $massimo;

$query = 'INSERT into '.$fulltabella.'(ultimoid,modo,richiedente,ip) values ('.$massimo.',"'.$modo.'","'.$richiedente.'","'.$_SERVER["REMOTE_ADDR"].'")';
$result = mysql_query($query, $db) or die(mysql_error('-11'));

$query='SELECT RELEASE_LOCK("lock_zconta");';
$result = mysql_query($query, $db) or die(mysql_error('-12'));

Ma ho notato che il LOCK è sempre libero per le varie connessioni, in quanto immagino che l'utente mysql-php sia sempre quello

Quindi cerco di formulare la richiesta così:
si può chiedere per mysql una LOCK TABLE WRITE con timeout?
E se no, come posso fare?

PS aggiungo che questo è ovviamente un frammento, in quanto il contatore lo devo manipolare come mi piace, mentre un autoincrementante "partirebbe" ed andrebbe avanti per i fatti suoi.
Ho tolto la logica che ritorna il contatore, più complessa di un semplice "+1", non è quello che mi interessa.

[in the web]

Prima di tutto, se posso permettermi, un consiglio generale: se usi funzionalità di MySQL che non sono troppo comuni (come LOCK TABLE e GET_LOCK(), appunto) leggi molto bene la documentazione, per capire come si usano e quali sono le loro limitazioni. In questo caso ti segnalo che GET_LOCK() è abbastanza intelligente, e il tuo IS_FREE_LOCK() introduce un bug; mentre LOCK TABLE non dovrebbe mai (MAI) essere usato con InnoDB. Per il semplice motivo che usano due logiche diverse, e anche se MySQL fa finta di poterle usare insieme, nella realtà penso che non più di 100 persone in tutto il mondo sappiano che cosa succede. E no, non sto esagerando.

La soluzione è semplicissima: usa le transazioni. Avvia la transazione in questo modo:

SET autocommit = 0;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

E alla fine concludi con:

COMMIT;

Al momento in cui leggerai la tabella, MySQL metterà un lock, e nessuno potrà inserire nulla finché non avrai finito. Se invece un altro thread avrà già acquisito un lock di questo tipo, questa transazione resterà in attesa e leggerà il valore massimo solo quando l'altra transazione sarà terminata. Quindi, come vedi, non c'è nessun conflitto.

[brancomat]

Grazie per la risposta, qualche altro chiarimento.
1) perchè IS_FREE_LOCK dovrebbe introdurre un bug?
E' superfluo, nel senso che GET_LOCK è sufficiente, ma se c'è già un LOCK attivo aspetta il timeout prima di "morire".
Certo è possibile che qualcuno mi faccia un GET_LOCK tra le due istruzioni (is_free_lock ... get_lock mie), ma in questo caso non succede nulla, in quanto (... credo...) la get_lock "mia" andrebbe in timeout.
In sostanza nel caso migliore mi ritorna prima al programma (se il lock esiste), nel caso peggiore impiega lo stesso tempo.
O almeno così l'ho interpretato io...

2) Il suggerimento sulle transazioni è buono, ma quello che non so è se esiste un modo per farla andare in timeout piuttosto rapidamente. Nella documentazione non ho trovato nulla; ho aperto un thread nell'area PHP per vedere se si può fare in PHP ma non ho avuto nessuna risposta...

3) Ricapitolando quello che vorrei fare è un contatore diffuso, che viene incrementato con logiche più o meno complicate da più client (PHP), i quali però devono NON devo rimanere bloccati indefinitivamente aspettando la risposta.
Mi è capitato con un hosting diffuso (molto economico) che una porzione del mio programma rimanesse "congelata" anche per una 20ina di secondi , ma non so come forzare una terminazione anomala in un tempo minore.
Ecco perchè il GET_LOCK mi era tanto piaciuto, o almeno inizialmente...

[in the web]

1) perchè IS_FREE_LOCK dovrebbe introdurre un bug?

Nel senso che, se il lock è già preso, invece di aspettare un timeout ti fermi, e non è quello che vuoi

2) Il suggerimento sulle transazioni è buono, ma quello che non so è se esiste un modo per farla andare in timeout piuttosto rapidamente.

Sono così complessi questi contatori? Sei sicuro che non ci sia qualcosa che non va? Comunque innodb_lock_wait_timeout è il numero di secondi che aspetti perché una connessione liberi un lock. Il default è 50. Io consiglio di impostarlo a un numero relativamente basso, come 5 o al massimo 10, e alzarlo a livello di sessione solo quando ha un senso.

Mi hai incuriosito con la storia dei contatori, se hai altri problemi raccontaci un po' meglio la situazione

[brancomat]

Se il LOCK è preso è giusto tornare subito con un messaggio d'errore, senza aspettare il timeout.

lock_wait... ecco finalmente qualcosa che inizia ad assomigliare alla soluzione!!! grazieee!!!!

I contatori riguardano la creazione di pratiche da parte di un consorzio che poi vengono date ai vari consorziati, i quali a loro volta li subappaltano a collaboratori esterni.
Per individuare chi abbia in carico la pratica si usano serie diverse tipo 1xxxxx sono le pratiche di tizio, 2xxxxx sono le pratiche di caio e così via. Tizio, Caio e gli altri possono anche prenotare le pratiche, ovvero prendersi un certo stock, che poi verranno compilate in seguito.

In pratica Tizio può riservarsi le pratiche 100010-100020 (ecco l'avanzamento del contatore che non è "+1" sempre), così quando Caio andrà a predisporre le pratiche sempre di tizio registrerà la 100021.

Spero grosso modo si capisca

[in the web]

Se il LOCK è preso è giusto tornare subito con un messaggio d'errore, senza aspettare il timeout.

Se fai GET_LOCK(xxx, 5), hai messo un timeout di 5 secondi. Con IS_FREE_LOCK il timeout non c'è. E' un po' contraddittorio

lock_wait... ecco finalmente qualcosa che inizia ad assomigliare alla soluzione!!! grazieee!!!!

Spero proprio che lo sia, oltre ad assomigliare! Se dovessi avere ancora problemi non cambiare tutto il codice, vediamo di capire dov'è il problema.

I contatori riguardano la creazione di pratiche da parte di un consorzio che poi vengono date ai vari consorziati, i quali a loro volta li subappaltano a collaboratori esterni.
Per individuare chi abbia in carico la pratica si usano serie diverse tipo 1xxxxx sono le pratiche di tizio, 2xxxxx sono le pratiche di caio e così via. Tizio, Caio e gli altri possono anche prenotare le pratiche, ovvero prendersi un certo stock, che poi verranno compilate in seguito.

In pratica Tizio può riservarsi le pratiche 100010-100020 (ecco l'avanzamento del contatore che non è "+1" sempre), così quando Caio andrà a predisporre le pratiche sempre di tizio registrerà la 100021.

Spero grosso modo si capisca

Grazie, mi hai tolto la curiosità

Consiglio: potresti inserire N righe con un'unica INSERT. E' un po' più efficiente perché, anche se devi inserire 50 record, PHP e MySQL si parlano una volta sola.

[brancomat]

Se fai GET_LOCK(xxx, 5), hai messo un timeout di 5 secondi. Con IS_FREE_LOCK il timeout non c'è. E' un po' contraddittorio

Non sono molto esperto, ma non vedo dove sia la contraddizione.
IS_FREE_LOCK torna subito un risultato, sia che il lock ci sia, sia no.
Non serve un timeout. Se so già che il LOCK esiste, allora è inutile cercare di fare un GET_LOCK per attendere che il timeout scada, e viceversa.
Come detto potrebbe esistere il caso in cui ci sia un GET/FREE lock, da parte di qualcun altro, mentre viene eseguito il codice tra IS_FREE e GET_LOCK. In questo caso l' "ottimizzazione" non serve a nulla, ma alla fine mi sembra una scelta comunque logica.

O no? ()

Consiglio: potresti inserire N righe con un'unica INSERT. E' un po' più efficiente perché, anche se devi inserire 50 record, PHP e MySQL si parlano una volta sola.

Francamente non so come fare, ma penso comunque di riuscirci, accodando le varie INSERT in una stringona PHP intervallate con ;.
Proverò

[in the web]

Non sono molto esperto, ma non vedo dove sia la contraddizione.
IS_FREE_LOCK torna subito un risultato, sia che il lock ci sia, sia no.
Non serve un timeout. Se so già che il LOCK esiste, allora è inutile cercare di fare un GET_LOCK per attendere che il timeout scada, e viceversa.
Come detto potrebbe esistere il caso in cui ci sia un GET/FREE lock, da parte di qualcun altro, mentre viene eseguito il codice tra IS_FREE e GET_LOCK. In questo caso l' "ottimizzazione" non serve a nulla, ma alla fine mi sembra una scelta comunque logica.

O no? ()

Allora: GET_LOCK(<lock_name>, <timeout>) controlla se <lock_name> è già preso; se è già preso, aspetta timeout secondi, che si liberi. Se non si libera, dopo che il timeout è scaduto restituisce 0. Se si libera, o se non era preso, restituisce 1 e l'esecuzione del programma può continuare. Come vedi fa tutto da solo, senza bisogno di IS_FREE_LOCK().

Se poi uno non vuole un timeout (ma perché?) basta passargli 0.

Francamente non so come fare, ma penso comunque di riuscirci, accodando le varie INSERT in una stringona PHP intervallate con ;.
Proverò

Sì.. se per esempio inserisci 3 righe la sintassi SQL è:
INSERT nome_tab (a, b) VALUES
(1, 2), (3, 4), (5, 6).

[brancomat]

Allora: GET_LOCK(<lock_name>, <timeout>) controlla se <lock_name> è già preso; se è già preso, aspetta timeout secondi, che si liberi. Se non si libera, dopo che il timeout è scaduto restituisce 0. Se si libera, o se non era preso, restituisce 1 e l'esecuzione del programma può continuare. Come vedi fa tutto da solo, senza bisogno di IS_FREE_LOCK().

Non è una necessità, è una ottimizzazione, o almeno così lo vedo io.
Supponiamo che IS_FREE_LOCK impieghi 0,1 secondi, GET_LOCK 0,2 secondi e il timeout sia 5 secondi.

Ricapitolo: ci sono 3 casi.
1. Il lock esiste
2. Il lock non esiste
3. Il lock viene preso da qualcuno durante l'esecuzione del mio script, esattamente tra il pezzo con IS_FREE e GET_LOCK

Nel caso 1. usando IS_FREE+GET_LOCK la durata è 0,1 secondi invece di 5,2. Quindi ci guadagno, e tanto.

Nel caso 2. con IS_FREE+GET_LOCK la durata è 0,3 secondi invece di 0,2 = 0,1 secondi in più. Quindi non ci perdo in sostanza nulla.

Nel caso 3. Se viene preso un LOCK (da qualcuno di diverso) tra IS_FREE e GET_LOCK allora il tempo è 5,3 secondi, invece di 5,2. Ci perdo un pochino, ma proprio poco, e devo essere davvero sfigato per il caso 3.

In pratica poca spesa... tanta resa...

[mydb]

Per il semplice motivo che usano due logiche diverse, e anche se MySQL fa finta di poterle usare insieme, nella realtà penso che non più di 100 persone in tutto il mondo sappiano che cosa succede. E no, non sto esagerando.

Ciao,

Mi hai incuriosito.

Puoi spiegarmi cosa succede?