Newsgroup: it.comp.lang.visual-basic
Titolo: Tabella contatore e relative Stored Procedure in SQL Server

Salve a tutti,
ho postato in precedenza su it.comp.software.database, ma nessuna risposta.
Devo aggiungere delle tabelle ad un db Sql Server 2000; decisione, per i
campi contatori, utilizzare una apposita tabella.
Ho scritto una s.p. (spContatoreIdentificativo) che gestisce la tabella
contatore (chiamata Counters).
Altre s.p. inseriscono i valori nelle rispettive tabelle, dopo aver
ottenuto il numero contatore dalla spContatoreIdentificativo.
Sotto sono riportate la spContatoreIdentificativo e una sp chiamante.
Pensate che detta accoppiata mi garantisce l’univocità dei campi contatore
?
La paura e che ad una prima lettura, della tabella, prima dell’update alto
utente legga il numero contatore.
‘’Stored Procedure utilizzata per inserire un nuovo record in una Tabella
ALTER PROCEDURE spPAT_NuovoRecord
@CODICEPAT varchar(50),
@DESCRIZIONE varchar(100),
@Risposta char(2) OUTPUT,
@IDKEYTAB int OUTPUT—numero contatore da chiedere alla S.P. chiamata
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT * FROM PAT WITH (UPDLOCK)
EXECUTE spContatoreIdentificativo
'PAT',@IDKEY=@IDKEYTAB OUTPUT
INSERT INTO PAT VALUES(@IDKEYTAB,@CODICEPAT,@DESCRIZIONE)
COMMIT TRANSACTION
‘’’Stored Procedure per gestire la Tabella contatore, così costruita:
Nome tabella: Counters
Campi: Counter_Name , varchar(60) --Nome della Tabella
NEXT_VALUE, int -- Campo contatore
ALTER PROCEDURE spContatoreIdentificativo
@Tabella varchar(100),--Nome tabella
@IDKEY int OUTPUT – Numero contatore da passare alla S.P. chiamante
AS
DECLARE @IDKEY_Interno int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
--Blocco il record della tabella
SELECT * FROM Counters WITH(ROWLOCK) WHERE Counter_Name = @Tabella 
--Leggo il valore del contatore della tabella 
SELECT @IDKEY_Interno = NEXT_VALUE FROM Counters WITH(ROWLOCK)
WHERE Counter_Name = @Tabella 
--Aumento di uno
SET @IDKEY_Interno = @IDKEY_Interno + 1
--Modifico il contatore della tabella
UPDATE Counters SET NEXT_VALUE=@IDKEY_Interno WHERE Counter_Name= @Tabella
SELECT @IDKEY=@IDKEY_Interno
COMMIT TRANSACTION
Grazie
giuseppeD
-- 
questo articolo e` stato inviato via web dal servizio gratuito 
http://www.newsland.it/news
 segnala gli abusi ad 
ab...@newsland.it

 Pensate che detta accoppiata mi garantisce l?univocità dei campi contatore
Se il tuo obiettivo è l'univocità, basterebbe un campo Identity 
autoincrementante, impostato come chiave univoca, e fa tutto 
Sql-Server.
L'unica cosa è che Sql-Server è autorizzato a 'lasciare dei buchi', per 
cui se ti serve una numerazione strettamente progressiva (numerazione 
fatture, ad esempio) va bene l'identity come chiave primaria, ma devi 
avere un campo 'numero documento' da incrementare a parte. Per questa 
cosa, io generalmente faccio l'insert con numero documento NULL, e 
subito dopo faccio un update del record appena inserito con un Max+1 
ricavato dalla tabella stessa. Siccome l'update è atomico, non può 
avvenire che due spid assegnino lo stesso numero.
Ciao
Alberto
-- 
Ma noi no

 avvenire che due spid assegnino lo stesso numero.
In questi casi, io, rilevo prima il Max+1 della tabella cui devo
aggiungere il nuovo record e poi inserisco.
Come esegui tu, devi rilevarti l'ultimo record. Non c'è pericolo che nel
frattempo altro abbia eseguito un nuovo inserimento ?
Ave


 frattempo altro abbia eseguito un nuovo inserimento ?
E' esattamente il contrario.
Cioè, tu basi tutto su una transazione. Che, in se, se ben isolata, 
offre la garanzia che vuoi. Ma basta che l'isolamento non sia fatto 
bene, che due transazioni partono insieme, ed eseguendo nello stesso 
ordine la select e poi l'insert, non vanno in blocco e ricavano e 
scrivono lo stesso valore.
Io invece faccio un update, cioè una singola operazione, in quanto tale 
atomica:
UPDATE TABELLA
SET CONTATORE=(SELECT MAX(CONTATORE) FROM TABELLA)+1
WHERE ID_RECORD=@ID_APPENA_INSERITO


 WHERE ID_RECORD=@ID_APPENA_INSERITO
@ID_APPENA_INSERITO č un identificatore che costruisci ?
Nel mio caso il contatore č anche identificatore del record.


 Nel mio caso il contatore è anche identificatore del record.
Come ti dicevo, ID_RECORD è qualcosa di automatico. Ad esempio, un 
Identity.
Quindi, quando faccio l'inserimento del record, eseguo un UNICO 
Adodb.Command che fa:
DECLARE @ID_APPENA_INSERITO NUMERIC(18,0)
INSERT INTO TABELLA
(CONTATORE, CAMPO1, CAMPO2)
Values(NULL, 'ciao', 'pippo')
SET @ID_APPENA_INSERITO=SCOPE_IDENTITY()
UPDATE TABELLA
SET CONTATORE=(SELECT MAX(CONTATORE) FROM TABELLA)+1
WHERE ID_RECORD=@ID_APPENA_INSERITO
Questa cosa può essere fatta in due modi:
- Transazionale, inserendo BeginTran e CommitTran alle estremità. A me 
personalmente non piace, perchè non si sa mai che da qualche altra 
parte ci sia qualcosa che opera a rovescio e quindi mi fa un deadlock. 
Preferisco rischiare di lasciare un record monco (cioè con 
Contatore=Null) che verrà poi pulito nelle manutenzioni periodiche.
- Non transazionale: come l'ho postata qui sopra. Azzeri il rischio di 
deadlock, ma rischi il record monco (con probabilità bassissima, ma non 
nulla). Il record monco non viene usato da nessuno, quindi un record su 
un milione non mi peggiora le performance.


 deadlock, ma rischi il record monco
Per curiosità accademica, potresti esplicitermi il caso del record monco,
che non riesco a "visualizzarlo"?
-- 
Salutoni
Sergio
Per rispondere... togli il group dall'indirizzo
Se i tempi non chiedono la tua parte migliore, inventa altri tempi. 
(Baolian, libro II, vv. 16-17)


 che non riesco a "visualizzarlo"?
Un record che contiene, rifacendoci all'esempio sopra:
ID_RECORD=123456
CONTATORE=NULL
CAMPO1='ciao'
CAMPO2='pippo'
In sostanza, un record in cui 'contatore' (da interpretarsi come 
'numero documento', altrimenti non avrebbe senso tutta la pappardella 
perchè basterebbe già ID_RECORD) non è mai stato inizializzato perchè 
il server è morto esattamente nel microsecondo fra l'insert e l'update.


 il server è morto esattamente nel microsecondo fra l'insert e l'update.
Ecco, era questo quello che mi mancava, il motivo per cui ci potesse
essere un record monco.
Direi che la probabilità è ragionevolmente bassa.


 WHERE ID_RECORD=@ID_APPENA_INSERITO
Mi sembra che si hanno due identificativi per una tabella.
Se la tabella è vuota, il contatore non aumenta, quindi necessita di un
controllo iniziale.


 controllo iniziale.
Era una scrittura a braccio, non un copiaincolla da un mio progetto. Se 
ci concentriamo sulla mancanza di un coalesce, anzichè sul concetto, 
perdiamo di costruttività.


 può sottostare a regole diverse, può anche essere duplicato).
Terrò presente quando si presenterà una costruzione simile.
Attualmente le tabelle che sto utilizzando hanno come identificativo il
contatore.
