sql - गंदा एक संग्रहीत प्रक्रिया से पढ़ता है




sql-server tsql (6)

आप किताबें ऑनलाइन में वर्णित के रूप में @variable = column = expression वाक्यविन्यास का उपयोग करके समस्या से बच सकते हैं। इसके अलावा, चूंकि स्टेटमेंट एक एकल-स्टेटमेंट स्वचालित लेन-देन में कार्यान्वित करता है, इसलिए आप स्पष्ट लेनदेन से बच सकते हैं।

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

CREATE PROCEDURE GetNextSerialIdentity
      @NextKey int output
    , @TableID int
AS
SET NOCOUNT ON;

UPDATE dbo.CfgSerialNumber
SET @NextKey = SerialNumber = SerialNumber + 1
WHERE SerialNumberID = @TableID;

IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 
                  16,1, @TableID);
END
GO

मेरे पास 100 धागे हैं जो प्रत्येक नीचे संग्रहीत कार्यविधि को बुलाते हैं।

मैं गंदी पढ़ता कैसे रोकूं?

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity
    (@NextKey int output, @TableID int)
AS
    declare @RowCount int, @Err int

    set nocount on

    select  
        @NextKey = 0

    begin transaction

Again:
    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber 
    SET CerealNumber = CerealNumber + 1  
    WHERE CerealNumberID = @TableID

    SELECT 
        @RowCount = @@RowCount, 
        @Err = @@Error      /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0            /* If Error gets here then exit         */
    BEGIN
        RAISERROR ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID)
        ROLLBACK TRANSACTION

        set nocount off
        return 1
    END

    IF @RowCount = 0                /* No Record then assume table is not   */
                                /* been initialized for TableID Supplied*/
    BEGIN
        RAISERROR('No Table Record Exists in CfgCerealNumber for ID:%d   ', 16, 1, @TableID)
        set nocount off
        Rollback Transaction
        return 1
    END

    /*Obtain updated Cereal number previously incremented*/
    SELECT @NextKey = CerealNumber 
    FROM CfgCerealNumber 
    WHERE CerealNumberID = @TableID

    SELECT @Err = @@Error                       /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0                            /* If Error gets here then exit         */
    BEGIN
        RAISERROR('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID)
        Rollback Transaction    
        set nocount off
        return 1
    END

    commit transaction
    set nocount off
    return 0
GO

ऐसा लगता है कि संग्रहीत कार्यविधि के इस भाग के समान मूल्य वापस 0.01% के आसपास लौटते समय समानांतर में चलते हैं:

SELECT @NextKey = CerealNumber 
FROM CfgCerealNumber 
WHERE CerealNumberID = @TableID

मैंने अपने कोड को ऐसे तरीके से संरचित कर दिया है ताकि लेनदेन में अद्यतन लपेटकर गंदी पढ़ा जाए।

मैं गंदी पढ़ता कैसे रोकूं?


sp_getapplock यह सुनिश्चित करेगा कि लेनदेन के पास एक अनन्य ताला है अगली धागे इसका उपयोग करने से पहले अद्यतन और पढ़ता है, इसलिए कोई गंदा पढ़ा नहीं हो सकता।

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

    ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
    AS
    declare @RowCount int, @Err int
    set nocount on
    select  @NextKey = 0
    begin transaction
   --ADDED CODE
    EXEC sp_getapplock @Resource='MyLock', @LockMode='Exclusive'
                , @LockOwner='Transaction', @LockTimeout = 15000
    Again:
    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  WHERE CerealNumberID = @TableID
    select  @RowCount = @@RowCount, @Err = @@Error      /*Obtain updated Cereal number previously incremented*/

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
        raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 
               16,1, @Err, @TableID)
                Rollback Transaction    
        set nocount off
        return 1
        end

    if @RowCount = 0                        /* No Record then assume table is not   */
                                    /* been initialized for TableID Supplied*/
        begin
        raiserror ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 
                      16,1, @TableID)
        set nocount off
                Rollback Transaction
        return 1
        end

    /*Obtain updated Cereal number previously incremented*/
    SELECT @NextKey = CerealNumber 
     From CfgCerealNumber WHERE CerealNumberID = @TableID

    select   @Err = @@Error                     /*Obtain updated Cereal number previously incremented*/

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
        raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 
               16,1, @Err, @TableID)
                Rollback Transaction    
        set nocount off
        return 1
        end

    commit transaction
    set nocount off
    return 0

एक विकल्प sp_getapplock सिस्टम संग्रहीत कार्यविधि का उपयोग करने के लिए होगा और एक संसाधन के लिए सीरियल की गई पहुंच सुनिश्चित करने के लिए SQL सर्वर के लॉकिंग में उपयोग किया जाएगा।

CREATE PROC MyCriticalWork(@MyParam INT)      
AS
    DECLARE @LockRequestResult INT
    SET @LockRequestResult=0

    DECLARE @MyTimeoutMiliseconds INT
    SET @MyTimeoutMiliseconds=5000--Wait only five seconds max then timeouit

    BEGIN TRAN

    EXEC @LockRequestResult=SP_GETAPPLOCK 'MyCriticalWork','Exclusive','Transaction',@MyTimeoutMiliseconds
    IF(@LockRequestResult>=0)BEGIN

        /*
        DO YOUR CRITICAL READS AND WRITES HERE
        */

        --Release the lock
        COMMIT TRAN
    END ELSE
        ROLLBACK TRAN   

बेकन बिट्स ने मुझे इसे मार दिया, लेकिन OUTPUT क्लॉज का उपयोग करना आपके रेसिंग मुद्दे के आसपास काम करने का सबसे आसान तरीका होगा। ऑफ लॉकिंग बंद करना एक विकल्प भी है, हालांकि मुझे लगता है कि इसमें थोड़ा अधिक ऊंचा होगा उस ने कहा है, एक IDENTITY स्तंभ या एक SEQUENCE का उपयोग इस कार्यक्षमता मैन्युअल रूप से लागू करने की कोशिश करने से इतना आसान है।

मैंने आपके कोड में जवाब डालने की स्वतंत्रता ली और कुछ टिप्पणियां जोड़ें:

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
AS
set nocount on

DECLARE @RowCount int, @Err int
DECLARE @output TABLE (NextKey int)

begin transaction

    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber WITH (UPDLOCK) 
       Set CerealNumber = CerealNumber + 1
    OUTPUT inserted.CerealNumber INTO @output (NextKey)
     WHERE CerealNumberID = @TableID

    select @RowCount = @@RowCount, /*Obtain updated Cereal number previously incremented*/ 
           @Err = @@Error      

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
            Rollback Transaction    
            raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16,1, @Err, @TableID)
            return -1
        end

    if @RowCount = 0                        /* No Record then assume table is not   */
                                    /* been initialized for TableID Supplied*/
        begin
            Rollback Transaction
            raiserror ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 16,1, @TableID)
            return -1
        end

COMMIT TRANSACTION


/*Obtain updated Cereal number previously incremented*/
SELECT @NextKey = NextKey 
 From @output

return 0
GO

टिप्पणियों:

  • संग्रहीत कार्यविधि से बाहर निकलने से पहले SET NOCOUNT OFF फिर से करने की कोई आवश्यकता नहीं है। जैसा कि आप दायरे से बाहर निकलते हैं, यह सेटिंग आपके द्वारा संग्रहीत कार्यप्रणाली को दर्ज करने से पहले वापस लौटा दी जाएगी।
  • मुझे यकीन नहीं है कि आपको WITH (UPDLOCK) ज़रूरत है, लेकिन यह निश्चित रूप से चोट नहीं पहुंचेगी।
  • मैंने लेनदेन को यथासंभव कम समय तक खुला रखा है, लेन-देन के अंदर तालिका-चर से मूल्य लाने का कोई कारण नहीं है।
  • मुझे लगता है कि पहले ROLLBACK करना सुरक्षित है और फिर एक RaisError() क्योंकि बाद में कुछ क्लाइंट सॉफ़्टवेयर द्वारा कनेक्शन को छोड़ा जा सकता है और / या आप TRY...CATCH । दोनों आज्ञाओं के प्रवाह को तोड़ देंगे और आप लेन-देन संख्या के साथ समाप्त हो जाएगा।
  • वाईएमएमवी लेकिन मुझे हमेशा एक त्रुटि के मामले में नकारात्मक रिटर्न कोड का उपयोग करने के लिए कहा गया है सकारात्मक वापसी-कोड का इस्तेमाल पंक्तियों की संख्या को इंगित करने के लिए किया जा सकता है .. हालांकि मैंने कभी इसे कभी अभ्यास में इस्तेमाल नहीं किया है।

जैसा कि पहले से उल्लेख किया गया है, आप स्वत: वृद्धि वृद्धि की कार्यक्षमता का उपयोग कर सकते हैं, जैसे पहचान स्तंभ या अनुक्रम।

यदि आप यह नहीं चाहते हैं, तो आपको सीरीयल तरीके से तालिका तक पहुंच बनाने की आवश्यकता है: एप्लिकेशन लॉक या अन्य क्षमताओं का उपयोग करना।

उदाहरण के लिए, आप इशारों को तालिका में पहली पहुंच (लेनदेन में) जैसे नीचे जोड़ सकते हैं:

UPDATE CfgCerealNumber
Set CerealNumber = CerealNumber + 1
FROM CfgCerealNumber with (tablockx, holdlock)
WHERE CerealNumberID = @TableID

यह तालिका के सभी समानांतर थ्रेड्स में अनुक्रमिक पहुंच की गारंटी देगा।


अगर आपको अपडेट करने और अपडेट करने की आवश्यकता है, तो मैं केवल OUTPUT खंड का उपयोग करेगा:

UPDATE CfgCerealNumber 
SET CerealNumber = CerealNumber + 1 
OUTPUT INSERTED.CerealNumber
WHERE CerealNumberID = @TableID;

यदि आपको अतिरिक्त जाँच की आवश्यकता है, तो आप संग्रहीत कार्यविधि से परिणाम सेट को वापस करने से पहले एक घोषित तालिका चर में OUTPUT कर सकते हैं।

एक अन्य विकल्प पहले मेज पर ब्लॉकिंग लॉक बनाना होगा, और फिर अपडेट करें:

SELECT @CerealNumber = CerealNumber + 1 
FROM CfgCerealNumber WITH (HOLDLOCK, UPDLOCK) 
WHERE CerealNumberID = @TableID;

UPDATE CfgCerealNumber
SET CerealNumber = @CerealNumber
WHERE CerealNumberID = @TableID;

लेकिन मैं पैसे नीचे डालूँगा कि मैंने इसे अभी भी समस्याएं पैदा की हैं। मुझे विश्वास है कि यह बहुत कम है





tsql