none
Consigli su gestione concorrenza RRS feed

  • 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 transazione

    saluti

    VP

    sabato 31 maggio 2014 22:37

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

    lunedì 2 giugno 2014 10:03
    Moderatore
  • 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
    lunedì 2 giugno 2014 12:29
  • 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

    saluti


    http://www.hotelsole.com/asql/index.php - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

    martedì 3 giugno 2014 00:55
    Moderatore
  • 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

    mercoledì 4 giugno 2014 12:10
  • 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/

    mercoledì 4 giugno 2014 22:09
    Moderatore
  • Ti ringrazio Andrea, sei stato molto chiaro.

    Grazie ancora.

    Ciao.


    -------- Mauri

    giovedì 5 giugno 2014 06:56