Con più domande
Consigli su gestione concorrenza

Domanda
-
Salve,
DB sql 2008 o sql 2012.
Dopo aver reperito il prossimo ID da una tabella A, devo eseguire con una transazione degli insert in altre tabelle in cui l'ID recuperato da A è uno dei valori di insert.
Domanda1:
Consigli come gestire al meglio una situazione del genere, calcolando il rischio della concorrenza, ma anche di una transazione andata male e tenendo conto che è un applicazione web che girerà su internet.
Domanda2:
Se avendo lo stesso scenario, prima di effettuare la transazione volessi mostrare a video il prossimo ID, come potrei tenerlo bloccato fino al buon fine della transazione, ed allo stesso tempo:
- gestire altre sessioni
- recuperarlo in una sessione successiva in caso di abbandono della sessione dalla parte dell'utente
- recuperarlo in una sessione successiva in caso di fallimento della transazionesaluti
VP
Tutte le risposte
-
per quale motivo devi tenere impegnato l'id successivo ?
per quale motivo devi recuperare quell'id se non viene utilizzato ?
non chiedere soluzioni di una situazione che tu hai già preimpostato e immaginato in un certo modo, esponi l'esigenza primaria perchè spesso altri costruiscono l'intera soluzione in maniera diversa dalla tua.
Edoardo Benussi
Microsoft MVP - Directory Services
edo[at]mvps[dot]org -
ok perfetto.
> per quale motivo devi tenere impegnato l'id successivo ?
Paura di concorrenza
> per quale motivo devi recuperare quell'id se non viene utilizzato ?
Perche mi serve per inserirlo in altre tabelle legate ad esso-
Spiego la mia esigenza.
Ho un sistema di contabilità (web) che crea un Numero di fattura del tipo NrFatt / Anno.
Per cui ogni fattura creata sarà del tipo NrFatt = Max(NrFatt) + 1
Vorrei gestire la concorrenza nella creazione di NrFatt, nel caso che 'N' utenti la richiedino al sistema nello stesso momento.
Avevo pensato a questa soluzione usando campo @Identity, ma il mio dubbio in questo caso sarebbe cosa mi succede in caso rollback? avrei un gap!
Suggerimenti su come gestirla?
Di seguito un esempio di cio che ho creato
CREATE TABLE [dbo].[tbInvoice]( [ID] [int] IDENTITY(1,1) NOT NULL, [Company] [nvarchar](3) NOT NULL, [Year] [nchar](4) NOT NULL, [InvoiceNumber] [int] NOT NULL, CONSTRAINT [PK_tbInvoice] PRIMARY KEY CLUSTERED ( [ID] ASC, [Company] ASC, [Year] ASC, [InvoiceNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] create Procedure sp_CreateNextInvoiceNr AS BEGIN SET XACT_ABORT ON DECLARE @nextNumReg int DECLARE @YEAR VARCHAR(4) SET @YEAR = (SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4))) BEGIN TRAN INSERT INTO [tbInvoice] ([Company] ,[Year] ,[InvoiceNumber]) VALUES ('001' ,@YEAR ,0) SET @nextNumReg = (SELECT [InvoiceNumber] + 1 FROM [tbInvoice] WHERE [Year] = DATEPART(YEAR,GETDATE()) AND ID = (SELECT SCOPE_IDENTITY() AS id ) - 1) IF(@nextNumReg =0 OR @nextNumReg ='' OR @nextNumReg IS NULL) BEGIN SET @nextNumReg =1 END UPDATE [tbInvoice] SET [InvoiceNumber] = @nextNumReg where ID =(SELECT SCOPE_IDENTITY() AS id ) COMMIT TRAN END
- Modificato VINPES lunedì 2 giugno 2014 12:32
-
salve Vincenzo,
in caso di failure, lo slot di [InvoiceNumber] sara' disponibile per il prossimo richiedente, mentre [ID] no, e restera' valorizzato come da ultimo inserimento fallito, quindi avrai dei gap, anche se cio' ovviamente e' irrilevante in quanto l'unica nunerazione progressiva senza gap deve essere [InvoiceNumber] ...
al di la' di cio' non comprendo tutta la logica...
INSERT INTO [tbInvoice] ([Company] ,[Year] ,[InvoiceNumber]) VALUES ('001' ,@YEAR ,0)
e' sbagliato in quanto: 1) Id e' un numero nel dominio degli interi e tu gli vuoi assegnare '001'.. 2) Id, oltre ad essere un intero, ha anche impostata la proprieta' identity, e quindi non devi (e non puoi direttamente, a meno di non impostare SET IDENTITY INSERT {ON|OFF}) fornirgli un valore in quanto questo sara' gestito e generato direttamente dallo storage engine al momento del tentativo di inserimento...
SET @nextNumReg = (SELECT [InvoiceNumber] + 1 FROM [tbInvoice] WHERE [Year] = DATEPART(YEAR,GETDATE()) AND ID = (SELECT SCOPE_IDENTITY() AS id ) - 1)
non comprendo il senso dell'istruzione... recuperi il numero [InvoiceNumber] incrementadolo di 1 filtrando per Anno (giusto) e Id = riga appena inserita?
io non agirei cosi', e recupererei dinamicamente l'informazione al momento dell'inserimento, qualche cosa simile a
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE [dbo].[tbInvoice]( [ID] [int] IDENTITY(1,1) NOT NULL, [Company] [nvarchar](3) NOT NULL, [Year] [nchar](4) NOT NULL, [InvoiceNumber] [int] NOT NULL, CONSTRAINT [PK_tbInvoice] PRIMARY KEY CLUSTERED ( [ID] ASC, [Company] ASC, [Year] ASC, [InvoiceNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY]; GO DECLARE @Company nvarchar(3) = 'abc'; INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber]) SELECT @Company, CONVERT(varchar(4),DATEPART(YEAR, GETDATE())) , ISNULL(MAX(base.InvoiceNumber), 0) + 1 FROM dbo.tbInvoice AS base WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE())); GO DECLARE @Company nvarchar(3) = 'abc'; INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber]) SELECT @Company, CONVERT(varchar(4),DATEPART(YEAR, GETDATE())) , ISNULL(MAX(base.InvoiceNumber), 0) + 1 FROM dbo.tbInvoice AS base WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE())); GO PRINT 'questa fallisce'; DECLARE @Company nvarchar(3) = 'abc'; INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber]) SELECT @Company + 'x', CONVERT(varchar(4),DATEPART(YEAR, GETDATE())) , ISNULL(MAX(base.InvoiceNumber), 0) + 1 FROM dbo.tbInvoice AS base WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE())); GO DECLARE @Company nvarchar(3) = 'abc'; INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber]) SELECT @Company, CONVERT(varchar(4),DATEPART(YEAR, GETDATE())) , ISNULL(MAX(base.InvoiceNumber), 0) + 1 FROM dbo.tbInvoice AS base WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE())); GO SELECT * FROM dbo.tbInvoice GO DROP TABLE dbo.tbInvoice; --<------- questa fallisce Msg 8152, Level 16, State 13, Line 4 String or binary data would be truncated. The statement has been terminated. ID Company Year InvoiceNumber ----------- ------- ---- ------------- 1 abc 2014 1 2 abc 2014 2 4 abc 2014 3
come vedi il terzo inserimento fallisce (al di la' del motivo), ma il gap nella numerazione di InvoiceNumber non c'e', mentre ovviamente c'e' nella colonna Id
ovviamente, visto che dopo l'inserimento in dbo.tbInvoice dovrai aggiungere righe in almeno altre 2 tabelle, racchiudi il tutto in una transazione esplicita, che ti consente di garantire adeguata protezione all'operazione ACID
perche' la colonna [Year] e' nchar? sicuramente non puo' essere un valore con carattere diverso da numeri, e quindi perche' non usare un intero con dominio vincolato da un costraint (BETWEEN 1999 AND 2050)... occupi "spazio" per niente ed il dominio dell'attributo e' sbagliato...
la chiave primaria, visto che hai utilizzato una chiave surrogata univoca basata su Identity (la colonna Id), la baserei eventualmente su quella, mentre farei un'altro indice su [Year] + [InvoiceNumber]...
la colonna [Company] nella chiave non ha tecnicamente alcun senso se non nel caso di tabella/database multiazienda e [Company] stia ad identificare un'azienda specifica... in tal caso farei la chiave primaria su [Company] + [Year] + [InvoiceNumber] e toglierei completamente l'attributo [Id]... l'unico motivo per mantenerlo e' per farci una chiave primaria clusterizzata visto che clusterizzare sul set di colonne [Company] + [Year] + [InvoiceNumber] sicuramente causerebbe elevata frammentazione
salutihttp://www.hotelsole.com/asql/index.php - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/
-
Scusa Andrea se mi intrometto, ma vorrei chiederti una delucidazione. Ho guardato la soluzione proposta da te e la trovo molto interessante perché potrebbe calzare a pennello per un applicativo che devo sviluppare, ho però un dubbio: nel caso di accesso contemporaneo di due o più utenti non c'è il pericolo che l'istruzione che identifica l'ultimo numero emesso, restituisca ad entrambi lo stesso numero? Se così fosse una o più istruzioni di inserimento sono destinate a fallire.
Sbaglio?Grazie.
Ciao
-------- Mauri
-
salve,
il precedente codice personalmente non lo userei in quanto, ovviamente, non restituisce "direttamente" il nuovo numero fattura da utilizzare negli inserimenti di dettaglio sicuramente dopo necessari... ma il concetto sicuramente lo utilizzo :) quindi ottengo il prossimo "numero" e quindi provvedo ad inserire anche le altre righe di dettaglio in tabelle relazionate...
la concorrenza... ovviamente c'e' il rischio ma comunque, come da te rilevato, il batch successivo ovviamente fallirebbe richiedendo un successivo roundtrip sul server... ma questo personalmente mi sta bene in quanto diversamente richiederebbe una protezione che non sempre sono disposto a pagare, quindi una transazione serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN
ed e' un costo probabilmente molto caro ... un rapido sommario del livello di isolamento puo' essere reperito in http://gavindraper.com/2012/02/18/sql-server-isolation-levels-by-example/
molto probabilmente resterei con il livello di isolamento di default visto che comunque i dati sono protetti dalle opportune chiavi primarie...
saluti
http://www.hotelsole.com/asql/index.php - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/
-