[mysql] valori auto_increment non sequenziali

[debug]

Ho una tabella dove, facendo uno stress test, ho inserito 1000 record.

La tabella ha questi campi:

+---------------------+---------------------+------+-----+---------+----------------+
| Field               | Type                | Null | Key | Default | Extra    |
+---------------------+---------------------+------+-----+---------+----------------+
| id                  | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| db_now_time         | datetime            | NO   | MUL | NULL    |    |
| db_sysdate_time     | datetime            | NO   |     | NULL    |    |
| php_gmdate_iso_8601 | varchar(30)         | NO   |     | NULL    |    |
| php_gmdate_rfc_2822 | varchar(35)         | NO   |     | NULL    |    |
| php_date_iso_8601   | varchar(30)         | NO   |     | NULL    |    |
| php_date_rfc_2822   | varchar(35)         | NO   |     | NULL    |    |
| php_time            | bigint(20)          | NO   |     | NULL    |    |
| php_microtime       | varchar(20)         | NO   |     | NULL    |    |
| extra_info          | varchar(255)        | YES  |     | NULL    |    |
+---------------------+---------------------+------+-----+---------+----------------+

come si può vedere, memorizzo anche i secondi e i microsecondi generati dallo script php.
In tutti i miei test, a partire dal record 300 circa, l'ID auto increment inizia a non essere più sequenziale, infatti se ordine i record per php_time e php_microtime, entrambi ASC, la lista degli id inizia ad essere:

+-----+
| id  |
+-----+
| 302 |
| 301 |
| 304 |
| 305 |
| 303 |
| 306 |
| 309 |
| 307 |
| 308 |
| 310 |
+-----+

(se invece non indico alcu Order By, l'ordine è quello della chiave primaria, ID).

Ho trovato pochissime informazioni in rete, le più interessanti si limitavano a dire che l'auto increment di per sè non assicura una numerazione sequenziale, piuttosto un valore univoco.
Però non capisco come logicamente e fisicamente sia possibile che un record venga memorizzato con un ID superiore rispetto a quello che viene memorizzato successivamente ad esso.

PS: ho fatto prove anche forzando il lock della tabella

Grazie


Ciaoooooo!!!!

[brancomat]

Ho avuto a che fare ultimamente con contatori e posso dirti quanto ho capito
1) non c'è alcuna garanzia nell'ordine delle righe del database.
quando ci sono inserimenti da più utenti, a seconda delle circostanze li mette tutti "in fila", ma anche no.
in locale, con un utente, gli inserimenti sono tutti ordinati, in concorrenza no
2) ci sono buchi nei campi autoincrementanti. è del tutto normale. l'unica garanzia è che sono sempre crescenti.
capita ad esempio se ci sono transazioni interrotte a metà, resta il "buco" nel numero
3) la select torna i dati in un ordine che dipende da come viene eseguita.
talvolta prende le righe nell'ordine di inserimento fisico, altre volte no (ad esempio se ci sono group by tipicamente saltano fuori ordinate alfabeticamente).

In ogni caso se non metti un order by NON hai alcuna garanzia sull'ordine delle righe. Nella prassi se selezioni per una chiave primaria i dati risultano ordinati con la select, ma è un caso, non è garantito.

[debug]

Ok, è quanto sapevo anch'io infatti.
Il mio obiettivo era quello di ottenere la certezza di poter estrarre l'ultimo inserimento nella tabella; normalmente ci si affida all'auto increment, oppure, se presente, ad un campo dedicato al datetime di inserimento, ma se parliamo di situazioni di grosso traffico aka inserimenti massivi in concorrenza, allora il dettaglio a livello di ora:minuti:secondi non è più sufficiente.
Avevo pensato perciò di usare i microsecondi, ma nella versione mysql che ho io non è ancora disponibile.
Ho usato quindi quelli restituiti da php (e anche qui sarebbe da aprire una parentesi tra le varie possibilità delle funzioni, gli arrotondamenti, i tipi di var restituite, etc.), ed è a questo punto che si sono evidenziati queste incongruenze.

L'unica spiegazione che mi sono dato è che il processo di inserimento, in quelle circostanze, è stato qualcosa di simile:

1. per generare il next autoincrement l'engine esegue un MAX(id)+1
2. il next autoincrement generato viene memorizzato temporaneamente nello schema db per poi essere usato per valorizzare il relativo campo
3. collo di bottiglia
4. nel frattemmpo viene memorizzata un'altra tupla, con lo stesso procedimento ovviamente, e che quindi prende o stesso next autoincrement precedentemente memorizzato dall'altro processo
5. il collo di bottiglia finisce e il primo processo riprende
6. al momento della memorizzazione si accorge che l'autoincrement non è valido poichè già presente
7. ne genera un altro e lo usa per memorizzare la tupla

in questo modo è comprensibile il motivo per cui un record viene memorizzato dopo rispetto al suo successore (in termini temporali).


Ciao!!!

[debug]

up

nessuno può confermarmi o smentire la mia supposizione?


Ciao!

[piero.mac]

Ok, è quanto sapevo anch'io infatti.
Il mio obiettivo era quello di ottenere la certezza di poter estrarre l'ultimo inserimento nella tabella;
....................................

http://it1.php.net/manual/en/functio...-insert-id.php

mysql o mysqli_insert_id() ti rendono esattamente l'id inserito dalla precedente query. Lo devi eseguire subito dopo l'insert mettendo ovviamente l'identificatore della connessione. non potra' essere l'id inserito da un'altra connessione.

Il campo deve essere autoincrement e del tipo INT. Hai degli esempi nel manuale.

[debug]

piero.mac grazie della risposta, ma il mio discorso entrava un po' più in profondità, non sono novello di database
Il fatto è, lo rispiego, che non c'è la sicurezza, a livello di mysql, che l'ultimo id presente nella tabella sia anche l'ultimo record inserito in ordine cronologico.
Ti risulta?
Se sì, per quale motivo?

Grazie


Ciaoo!!

[piero.mac]

i record inseriti in una tabella hanno una posizione fisica random per definizione. quindi l'ultimo record fisico potrebbe non essere l'ultimo inserito. In caso di cancellazione di record MySql recupera lo spazio libero a meno che tu non abbia eseguito nel frattempo l'optimize table che compatta gli spazi vuoti.

Il record viene individuato dall'id (autoincrement). Il valore piu' alto e' l'ultimo inserito. Su questo non ci piove.
Una data ora potrebbe invece essere falsata dal fatto che in un inserimento multiplo (stessa query) l'ora di sistema viene letta una sola volta e applicata a tutto il range inserito / modificato e quindi potrebbero risultare piu' record con lo stesso valore.

Se vuoi sempre l'ultimo inserito da te usa mysql_insert_id() subito dopo la query. Se vuoi l'ultimo inserito da chiunque usa l'order by id desc, limit 1

[debug]

Il record viene individuato dall'id (autoincrement). Il valore piu' alto e' l'ultimo inserito. Su questo non ci piove.
Una data ora potrebbe invece essere falsata dal fatto che in un inserimento multiplo (stessa query) l'ora di sistema viene letta una sola volta e applicata a tutto il range inserito / modificato e quindi potrebbero risultare piu' record con lo stesso valore.

Questo però cozza con i miei test, che ho riportato nel primo post, in cui ci sono diversi valori di microsecondi, che in alcuni casi mostra una anomalia in quanto l'id X risulta avere maggiori microsecondi dell'id X+1, e non parliamo di range, parliamo di valori del tutto diversi tra loro.
Nella documentazione ufficiale non ho trovato nulla che non vada più in la della mera spiegazione ordinaria, cioè quella di base che hai postato tu prima, nulla di più specifico.


Ciaoo!!

[piero.mac]

Ma i microsecondi sono gestiti da mysql? O sono solo per compatibilita' SQL?

Le query vengono serializzate e quindi inserite una dopo l'altra. Il tuoi problema credo derivi dal fatto che usi bigint che e' un valore assurdo per un id e mal gestito da mysql nel senso che dipende anche dal PC e OS e in effetti potrebbe non essere completamente affidabile per un autoincrement.

Prova a rifare le prove usando INT unsigned al posto di bigint. Son sempre gestiti oltre 4 miliardi di record che e' piu' che sufficente nella maggior parte dei casi.

[debug]

Come scritto all'inizio, i microsecondi li genero con php, vista che la versione di mysql in mio possesso ancora non li supporta.

Proverò con INT come consigliato.

Grazie

Ciaoooooo!!!!!!!!