SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use of Identity Property to Resolve Concurrency Issues


Use of Identity Property to Resolve Concurrency Issues

Author
Message
dvdwouwe 72544
dvdwouwe 72544
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 33
Hi,

Can i ask what database you used that had locking hints in 1991?
I thought this was not added until SQL92 in 1992, but 20 years is a long time.


I use SQL server from version 2005, but I use DB-systems from the 90's, nl. Oracle and Interbase.
And Interbase did have some unbelievable powerful transaction management, and Inprise screwed this powerfull system.
And from 2001 I use also open-source Firebird DB (what is a fork of Interbase) and PostgreSQL. I studied the source files of firebird for 8 years and learned a lot how an DB-engine works inside.

And I believe that Interbase/Firebird introduced the system what is introduced in the Yukon engine as read-committed snapshot for over 20 years ago.

Using different database systems give me other ideas how to solve solutions.

And there is a huge difference between optimistic en pessimistic DB-systems.
  • PostgreSQL/ Oracle / Interbase / firebird are optimistic (readers don't block writers and writers don't block readers), but they have special anomalies like read/write skew

  • DB2 / SQL-server are pessimistic DB-servers (a lot of locks)


  • So, locks (or lock hints) aren't always the solution to do the work, only in some very special cases. And DB-systems are getting smarter and smarter if you feed them with correct information (constrainst etc..) to reduce the manual interactions of giving hints.

    I did some sessions in Belgium about the key difference between those systems to database developers.

    Danny
    Vladimir Sotirov
    Vladimir Sotirov
    SSC Eights!
    SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

    Group: General Forum Members
    Points: 815 Visits: 138
    I think your solution is really good. One more time DBA has to solve issues caused by developers that do not know that database calls are expensive and locking is not so difficult to be implemented on application side. It would be better if the developer creates a call to the database and gets 100 keys at one time that he stores in memory in his application and uses the keys from there. Now instead of 100 calls to the database for each record he makes only one for each 100 records.
    Grigore Dolghin
    Grigore Dolghin
    Grasshopper
    Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

    Group: General Forum Members
    Points: 24 Visits: 30
    I am afraid this approach is even worse. What if for each of those keys the developer has to insert some records in a related table, then use those keys to insert related records in a third table? and what if those keys are actually autoincrement values and can't be obtained beforehand? and what if the values have to be generated taking in account the other possible users working in the same time? (such as secvential invoice numbers?)

    Locking must be implemented on database side, period.

    Microsoft MVP 2006-2010
    Solomon Rutzky
    Solomon Rutzky
    Hall of Fame
    Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

    Group: General Forum Members
    Points: 3248 Visits: 3029
    Ajit, this is an interesting approach to the problem, but most likely it is just adding complication via over-engineering when a much simpler solution appears to be available.

    1) The most important and easiest thing to do is, as "dvdwouwe 72544" mentioned, create a PK on the "key" column. There is really no reason to have this table without a PK defined. This will also ensure that a duplicate "key" doesn't accidentally get added to the table.

    2) You don't need to worry about the SELECT of the new value getting something updated from another session if you use the OUTPUT clause in the UPDATE as follows:


    UPDATE tbl_kvp
    SET column_value += 1
    OUTPUT inserted.column_value
    WHERE column_key = @key



    This avoids the need for additional locking as "tech.dbmeyer" had suggested.

    3) You can always tell the system to only lock a single row (although given the singular nature of the UPDATE statement I am not sure why it would ever do anything else, but still) by using the WITH (ROWLOCK) hint in the UPDATE as "SanDroid" mentioned.


    Minor note: I find it interesting / amusing that whoever created this table (a developer I think you said) prefixed the column names with "column_". ;-)

    SQL# - http://www.SQLsharp.com/
    irozenberg
    irozenberg
    SSC-Enthusiastic
    SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

    Group: General Forum Members
    Points: 124 Visits: 145
    Ironically a new version of SQL Server would have an Oracle-style SEQUENCE identity capability, so hopefully there will not be need to "re-invent the wheel"
    Ajit Ananthram
    Ajit Ananthram
    SSC Journeyman
    SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

    Group: General Forum Members
    Points: 83 Visits: 282
    Solomon Rutzky (2/26/2011)
    Ajit, this is an interesting approach to the problem, but most likely it is just adding complication via over-engineering when a much simpler solution appears to be available.

    1) The most important and easiest thing to do is, as "dvdwouwe 72544" mentioned, create a PK on the "key" column. There is really no reason to have this table without a PK defined. This will also ensure that a duplicate "key" doesn't accidentally get added to the table.

    2) You don't need to worry about the SELECT of the new value getting something updated from another session if you use the OUTPUT clause in the UPDATE as follows:


    UPDATE tbl_kvp
    SET column_value += 1
    OUTPUT inserted.column_value
    WHERE column_key = @key



    This avoids the need for additional locking as "tech.dbmeyer" had suggested.

    3) You can always tell the system to only lock a single row (although given the singular nature of the UPDATE statement I am not sure why it would ever do anything else, but still) by using the WITH (ROWLOCK) hint in the UPDATE as "SanDroid" mentioned.


    Minor note: I find it interesting / amusing that whoever created this table (a developer I think you said) prefixed the column names with "column_". ;-)


    Thanks for your feedback Solomon.

    Even with the primary key in place, if two user sessions would attempt to increment the same key as part of their own transactions, one session would end up blocking the other.

    Ideally, incrementing the KVP value should be done outside transactions, but applications developers seldom adhered to this principle. As I've said in previous posts, the main goals for me were to eliminate this blocking, and also make the incrementing process independent of transactions.

    By making the single horizontal record for a given key vertical, transactions could increment the KVP and obtain appropriate values without blocking each other.

    Ajit Ananthram
    Blog - http://ajitananthram.wordpress.com
    Paul White
    Paul White
    SSC-Dedicated
    SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

    Group: General Forum Members
    Points: 34096 Visits: 11359
    Solomon Rutzky (2/26/2011)
    ...You don't need to worry about the SELECT of the new value getting something updated from another session if you use the OUTPUT clause in the UPDATE as follows:


    UPDATE tbl_kvp
    SET column_value += 1
    OUTPUT inserted.column_value
    WHERE column_key = @key



    This avoids the need for additional locking as "tech.dbmeyer" had suggested.

    Yes, but the problem is that the key-allocation procedure might be called within a transaction. The exclusive row lock needed by the statement above will be held to the end of that transaction. An application that starts a transaction, allocates a key from this procedure, and then sits around waiting for something, will block anyone else needing the next key from the same sequence.

    I wrote about this issue at length (including robust ways to pre-allocate a range of keys without blocking) in:
    http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx



    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Paul White
    Paul White
    SSC-Dedicated
    SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

    Group: General Forum Members
    Points: 34096 Visits: 11359
    It is possible to avoid the dummy column, ever-growing number of rows in the hidden tables, and avoid locking issues by using an approach based on this example code: (the example just uses a single range, but it is easy to extend the idea)


    USE tempdb;
    GO
    -- Drop the table if it exists from a previous run
    IF OBJECT_ID(N'dbo.SneakyIdentity', N'U') IS NOT NULL
    BEGIN
    DROP TABLE dbo.SneakyIdentity;
    END;
    GO
    -- Create the hidden table used to allocate IDs
    CREATE TABLE dbo.SneakyIdentity (row_id BIGINT IDENTITY NOT NULL);
    GO


    -- The allocation routine
    CREATE PROCEDURE dbo.Allocate
    (
    @ID BIGINT OUTPUT
    )
    AS
    BEGIN
    -- No DONE_IN_PROC messages
    SET NOCOUNT ON
    ;
    -- Almost all errors will abort the batch
    SET XACT_ABORT ON
    ;

    -- Table variable used with the OUTPUT clause
    -- to safely read the identity value assigned.
    -- Issues (bugs) have arisen in the past with
    -- @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT.
    DECLARE @Output TABLE
    (row_id BIGINT NOT NULL)
    ;

    -- Start error handler
    BEGIN TRY
    -- Might not be the top-level transaction!
    BEGIN TRANSACTION;

    -- Save point
    SAVE TRANSACTION AllocateID;

    -- Allocate an ID using the 'hidden' table
    INSERT dbo.SneakyIdentity
    OUTPUT inserted.row_id
    INTO @Output (row_id)
    DEFAULT VALUES;

    -- Rollback to the save point
    ROLLBACK TRANSACTION AllocateID;

    -- Correct the transaction count
    COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH

    -- Remember the error message
    DECLARE @ErrMsg NVARCHAR(2048);
    SET @ErrMsg = ERROR_MESSAGE();

    -- Uncommittable transaction => unconditional rollback
    -- (this is the only option)
    IF XACT_STATE() = -1 ROLLBACK TRANSACTION;

    -- If our transaction is still alive, roll back to
    -- the save point, and adjust @@TRANCOUNT
    IF XACT_STATE() = 1
    BEGIN
    -- Rollback to the save point
    ROLLBACK TRANSACTION AllocateID;

    -- Correct the transaction count
    COMMIT TRANSACTION;
    END

    -- Custom error handling goes here
    RAISERROR('Error in allocation: %s', 16, 1, @ErrMsg);

    -- Error
    RETURN 999;

    END CATCH;

    -- Capture assigned value
    -- (Table variables are not affected by transaction rollbacks)
    SET @ID =
    (
    SELECT TOP (1)
    row_id
    FROM @Output
    );

    -- Success
    RETURN 0
    ;
    END;
    GO


    -- === TEST ===

    -- Variables
    DECLARE @ID BIGINT,
    @RC INTEGER;
    ;
    -- Simulate an enclosing user transaction
    BEGIN TRANSACTION
    ;
    -- Allocate the next id
    EXECUTE @RC =
    dbo.Allocate @ID OUTPUT
    ;
    -- Show locks
    SELECT *
    FROM sys.dm_tran_locks AS TL
    WHERE TL.request_session_id = @@SPID
    ;
    -- Show procedure return code and allocated ID
    SELECT return_code = @RC,
    allocated_id = @ID
    ;
    -- Now roll back or commit
    ROLLBACK
    ;
    -- Uncomment this when finished
    -- DROP TABLE dbo.SneakyIdentity;
    -- DROP PROCEDURE dbo.Allocate;





    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    James Goodwin
    James Goodwin
    SSCommitted
    SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

    Group: General Forum Members
    Points: 1651 Visits: 1107
    That's a fairly nice solution to the problem, however...(There's always a but...)

    The biggest problem I see with your approach is that when someone wants to add a new key-value pair they also need to add a new table. I dislike needing to alter the design of the database just because someone wants to add a data value.

    Therefore: Wouldn't it be easier to use a single nextnum table instead of one for each key? Your get sequence number code would work the same and you wouldn't need to use dynamic SQL.
    --
    JimFive
    UMG Developer
    UMG Developer
    Hall of Fame
    Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

    Group: General Forum Members
    Points: 3928 Visits: 2204
    James Goodwin (2/28/2011)
    That's a fairly nice solution to the problem, however...(There's always a but...)

    The biggest problem I see with your approach is that when someone wants to add a new key-value pair they also need to add a new table. I dislike needing to alter the design of the database just because someone wants to add a data value.

    Therefore: Wouldn't it be easier to use a single nextnum table instead of one for each key? Your get sequence number code would work the same and you wouldn't need to use dynamic SQL.


    That is what they started with but they had locking and blocking issues while updating that one nextnum table to obtain the next key value.

    I suspect that they would only add a new key when they add a new table, so instead of just adding one table you add two. Is the solution perfect? No, but it fixed the problem they were encountering without having to do a major redesign of the database or the application.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search