[SQL] riordinare id

[Fabiorayden]

Col passare del tempo i valori degli id hanno raggiunto quote elevate (da 10000 in poi, mentre i record sono poco più di un centinaio).
E' possibile far sì che ad un successivo inserimento l'id assuma il valore più basso disponibile?

[gugoXX]

Certo, e' possibile.
Ma se poi con l'introduzione di 10000 nuovi record dovessi raggiungere di nuovo uno dei 100 ID che ora e' presente (e suppongo anche poi), cosa vorresti che capitasse?

[Fabiorayden]

Certo, e' possibile.
Ma se poi con l'introduzione di 10000 nuovi record dovessi raggiungere di nuovo uno dei 100 ID che ora e' presente (e suppongo anche poi), cosa vorresti che capitasse?

che salti al primo valore disponibile.
In pratica ho bisogno che la prossima riga che inserisco avrà id=1, quando poi troverà un id che è già occupato (presupponiamo id=90) dovrà saltarlo (e quindi assumere id=91 se è libero).

[gugoXX]

che salti al primo valore disponibile.
In pratica ho bisogno che la prossima riga che inserisco avrà id=1, quando poi troverà un id che è già occupato (presupponiamo id=90) dovrà saltarlo (e quindi assumere id=91 se è libero).

Lo prevedevo.
La cosa che vorresti fare non si puo' fare automaticamente.
Dovresti costruire una funzione apposita.
Ma il supportare un database di tale funzione non e' banale. Occorre affrontare problemi di concorrenza e di lock, che sono gia' risolti dalle identity di SqlServer e Access, dalle Sequence di Oracle, e in generale da tutti i contatori presenti nei database piu' diffusi.
Ergo, ti consiglio di tenere gli ID alti, che tanto il database non soffre.

[amedeoviscido]

Per curiosità, che tipo di db usi? Potresti risolvere con una semplice stored procedure.

[gugoXX]

Per curiosità, che tipo di db usi? Potresti risolvere con una semplice stored procedure.

Ciao, Quale algoritmo proporresti nella stored procedure?

[amedeoviscido]

Dipende, se sai che questo problema non si presenterà più, potresti scrivere una query ad hoc, metti il caso l'id iniziale che hai adesso è 10000, potresti fare una cosa del genere nella procedura "genera_id":

SELECT max(id)+1 INTO massimovalore FROM tabella WHERE id<10000

In questo modo ottieni il massimo valore al di sotto dei 10000:
se massimovalore vale 10000 allora il massimovalore deve diventare

SELECT max(id)+1 INTO massimovalore FROM tabella

Se il tuo engine DB supporta le query multiple (eseguite in modalità atomica) potresti fare una cosa del tipo

INSERT INTO tabella(id,altricampi) VALUES ((SELECT genera_id),'altri campi')

Quindi il tuo problema e' scrivere la procedura.

Altra situazione invece si presenta se gli ID vanno e vengono: quì la cosa si fà più complicata. Il mio suggerimento è di crearti una tabella "ID_CANCELLATI" ed ogni volta che cancelli un record dal DB inserisci l'ID relativo in questa tabella; poi quando devi creare un nuovo record puoi prima andare a vedere se la tabella ID_CANCELLATI è vuota; in qual caso prenderesti il valore max(id) altrimenti il primo valore da ID_CANCELLATI.

[gugoXX]

Non mi sembra che le soluzioni siano resistenti al multiprocesso.
Mi spiego, cosa succede se 2 processi aprono contemporaneamente una transazione e cercano di inserire entrambi un record in quella tabella, e poi chiudono (commit) la transazione?

[amedeoviscido]

Dipende dall'engine DB, come ho già detto.
Ad esempio, in PostgreSQL le query singole rispettano le proprietà acide delle transazioni: quindi se una query chiama una stored procedure che a sua volta ne chiama altre 100 il tutto sarà eseguito come una transazione. In pratica, è come se facesse "BEGIN TRANSACTION; QUERY; COMMIT WORK;". Quindi, se all'interno della query chiami una SP (come "SELECT genera_id") allora quella query sarà eseguita come transazione.

[gugoXX]

Dipende dall'engine DB, come ho già detto.
Ad esempio, in PostgreSQL le query singole rispettano le proprietà acide delle transazioni: quindi se una query chiama una stored procedure che a sua volta ne chiama altre 100 il tutto sarà eseguito come una transazione. In pratica, è come se facesse "BEGIN TRANSACTION; QUERY; COMMIT WORK;". Quindi, se all'interno della query chiami una SP (come "SELECT genera_id") allora quella query sarà eseguita come transazione.

Sisi, avevo capito.
Ma quello che proponevo di testare e':
- Ho due processi, ciascuno dei quali apre esplicitamente una transazione
- Entrambi giungono contemporaneamente ( prima che l'altro abbia effettuato il commit) al punto di dover inserire un record nella tabella
- Il secondo dei due processi non puo' vedere che l'altro ha gia' inserito un record, e che quindi il proprio MAX dovrebbe essere diverso da quanto calcolato.
- Cosa succede?

In SQL Server ed in Oracle se si usa tale metodo viene generato un LOCK di tutta la tabella. (Cosa che con le identity e le sequence invece non succede)
Quando si generano LOCK su tutta la tabella e' molto facile degenerare in DEADLOCK senza neppure che ce ne si accorga.
Gestire in modo robusto questi casi non e' cosi' semplice.

[amedeoviscido]

Beh si. Ti dirò che non ricordo esattamente come funziona il metodo con il quale vengono gestite le transazioni, mi ricordo di averlo studiato nell'esame di sistemi informativi, ma se non erro ogni volta che una query scrive su un record o lo elimina, siccome il sistema è in grado di riportare indietro lo stato di altre transazioni, dovrebbe annullare quelle che hanno letto lo stesso valore ma che non sono ancora terminate.

Detto ciò poi dipende dal problema che stiamo risolvendo: non sappiamo se l'applicazione è multiutente o meno! L'utente che ha aperto il thread non mi sembra l'abbia scritto...

[Fabiorayden]

Oggi ero alle prese con altri problemi e non ho potuto provare le vostre soluzioni.
Appena potrò farò le dovute considerazioni.

Grazie per l'aiuto

[cdimauro]

Sull'argomento transazioni et similia vi consiglio di leggere questo http://www.firebirdsql.org/doc/whitepapers/fb_vs_ibm_vs_oracle.pdf documento, che è MOLTO interessante ed esaustivo.