Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Concurrency Issues

    Is there any way to get the sample below working so that both "threads" are guaranteed to get unique and incrementing values?

    I'm suspecting the answer is no. You can use transactions on completely database oriented operations that read/write to a database and complete. But there aren't complete synchronization controls for operations like below that try to return a value to an outside process.

    Code:
    IF OBJECT_ID('SimpleTable') IS NOT NULL
    	DROP TABLE SimpleTable
    
    CREATE TABLE SimpleTable (
    	A INTEGER
    )
    INSERT INTO SimpleTable (A) VALUES (1)
    
    -- Run in one window
    DECLARE @value INTEGER
    
    BEGIN TRANSACTION
    SELECT TOP 1 @value = A FROM SimpleTable
    WAITFOR DELAY '00:00:05'
    UPDATE SimpleTable SET A = @value + 1
    COMMIT TRANSACTION
    
    SELECT @value
    SELECT A FROM SimpleTable
    
    -- Run in a second window
    DECLARE @value INTEGER
    
    BEGIN TRANSACTION
    SELECT TOP 1 @value = A FROM SimpleTable
    UPDATE SimpleTable SET A = @value + 1
    COMMIT TRANSACTION
    
    SELECT @value
    SELECT A FROM SimpleTable

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Pat Phelan
    Use an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.

    -PatP
    Actually, I did just that. However, I was kind of curious if it was possible to acheive with transactions or with some explicit locking calls.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There definitely is a way to do it using just SQL statements with Transact-SQL's locking model. The identity process is simpler and supported though.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by rdjabarov
    You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.
    Thank you. That will work. Except BOL calls those locking "hints" which means the solution isn't guaranteed to work even though it probably will on today's implementations.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't see how it will not work, but you're free to refute it.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Oct 2003
    Posts
    268
    It won't work if a future implementation (either a future version or service pack of SQL Server) decides to ignore the locking "hint". The database engine is completely free to obey or ignore "hints" at will so you shouldn't base an algorithm on that.

    Quote Originally Posted by rdjabarov
    I don't see how it will not work, but you're free to refute it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •