Is there a row locking similar to Oracle’s row locking in SQL SERVER 2016

  • nickosl50

    SSC Enthusiast

    Points: 129

    I would like to  lock a certain row in a table that stores the next available numbers for various types of invoices in an inventory app.
    Is there a row locking similar to Oracle’s row locking in SQL SERVER 2016?

    Thank you very much
    Nick

  • subramaniam.chandrasekar

    SSCarpal Tunnel

    Points: 4108

    nickosl50 - Friday, February 23, 2018 1:12 AM

    I would like to  lock a certain row in a table that stores the next available numbers for various types of invoices in an inventory app.
    Is there a row locking similar to Oracle's row locking in SQL SERVER 2016?

    Thank you very much
    Nick

    I too have been waiting for the similar post, Just waiting for other experts to comment.

  • andrew gothard

    SSChampion

    Points: 12127

    Hi,
        personally, this question is one which (possibly wrongly) suggests a probable underlying design issue to me.  Would you be able to describe your problem a little more fully?  What format are your identifiers taking – how are they calculated, for example, what datatype(s) are they? 
        In general, manual row locking is very much a ‘best avoided’ process.  I have seen this approach cause significant bottlenecks in not enormously busy systems for a start

    cheers
    Andrew

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • sgmunson

    SSC Guru

    Points: 110411

    andrew gothard - Friday, February 23, 2018 3:13 AM

    Hi,
        personally, this question is one which (possibly wrongly) suggests a probable underlying design issue to me.  Would you be able to describe your problem a little more fully?  What format are your identifiers taking - how are they calculated, for example, what datatype(s) are they? 
        In general, manual row locking is very much a 'best avoided' process.  I have seen this approach cause significant bottlenecks in not enormously busy systems for a start

    cheers
    Andrew

    I agree… intentional row locking is a good way to create a problem, and NOT a good way to solve one.   I have to wonder why a sequence isn’t being used.   Oracle has had them available for a rather long time, and SQL Server has had them since SQL 2014.   They usually get used to help solve these kinds of problems.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • nickosl50

    SSC Enthusiast

    Points: 129

    I have a table  ( invoiceTypes)  that stores various invoice types and for each type its next available number (integer)
    When I enter a new invoice ( in a table invoices) and press save I start a transaction in which I want to get the next available number from invoiceTypes table  for the type of invoice I entered. I want to be sure that no other user gets the same number for this invoice type (since (invoice type and number) is a unique key for invoices table). After I  commit my transaction other users can access the invoiceTypes table.

    I would be grateful if you could propose me a solution to the above problem.

    Thank you

  • Chris Harshman

    SSC-Forever

    Points: 41584

    nickosl50 - Friday, February 23, 2018 10:30 AM

    I have a table  ( invoiceTypes)  that stores various invoice types and for each type its next available number (integer)
    When I enter a new invoice ( in a table invoices) and press save I start a transaction in which I want to get the next available number from invoiceTypes table  for the type of invoice I entered. I want to be sure that no other user gets the same number for this invoice type (since (invoice type and number) is a unique key for invoices table). After I  commit my transaction other users can access the invoiceTypes table.

    I would be grateful if you could propose me a solution to the above problem.

    Thank you

    As Steve already mentioned, this sounds like the exact situation that a SEQUENCE would be used for.  You can create a different SEQUENCE for each type of Invoice that you need to track:
    https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers

  • Luis Cazares

    SSC Guru

    Points: 183476

    Chris Harshman - Friday, February 23, 2018 11:01 AM

    nickosl50 - Friday, February 23, 2018 10:30 AM

    I have a table  ( invoiceTypes)  that stores various invoice types and for each type its next available number (integer)
    When I enter a new invoice ( in a table invoices) and press save I start a transaction in which I want to get the next available number from invoiceTypes table  for the type of invoice I entered. I want to be sure that no other user gets the same number for this invoice type (since (invoice type and number) is a unique key for invoices table). After I  commit my transaction other users can access the invoiceTypes table.

    I would be grateful if you could propose me a solution to the above problem.

    Thank you

    As Steve already mentioned, this sounds like the exact situation that a SEQUENCE would be used for.  You can create a different SEQUENCE for each type of Invoice that you need to track:
    https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers

    Although, if the numbers are already stored in a table, an update that assigns value to a variable would work if it’s inside a transaction. This shouldn’t create problems unless you start using NOLOCK hints or READ_UNCOMMITED isolation level everywhere.


    UPDATE t SET
        @InvoiceNumber = InvoiceNumber = InvoiceNumber + 1
    FROM InvoiceTypes
    WHERE InvoiceType = @InvoiceType;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ScottPletcher

    SSC Guru

    Points: 97741

    Yes, there is.  It’s called “SNAPSHOT ISOLATION”.  That’s almost exactly like Oracle: point-in-time for SELECTs, version rows stored to handle long-running queries, readers don’t block writers, writers don’t block readers, etc.  SQL offers two way to implement it.

    1) Set the entire db to SNAPSHOT (SET READ_COMMITTED_SNAPSHOT ON).  All trans using normal READ COMMITTED will run as snapshot, with no app changes required.

    2) Set db to ALLOW_SNAPSHOT_ISOLATION.  Individual trans can then specify snapshot or not.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • subramaniam.chandrasekar

    SSCarpal Tunnel

    Points: 4108

    ScottPletcher - Friday, February 23, 2018 12:51 PM

    Yes, there is.  It's called "SNAPSHOT ISOLATION".  That's almost exactly like Oracle: point-in-time for SELECTs, version rows stored to handle long-running queries, readers don't block writers, writers don't block readers, etc.  SQL offers two way to implement it.

    1) Set the entire db to SNAPSHOT (SET READ_COMMITTED_SNAPSHOT ON).  All trans using normal READ COMMITTED will run as snapshot, with no app changes required.

    2) Set db to ALLOW_SNAPSHOT_ISOLATION.  Individual trans can then specify snapshot or not.

    If I’m not wrong, the default isolation level of SQL Server 2014 is READ COMMITTED SNAPSHOT. Please correct me if I’m begin wrong.

  • nickosl50

    SSC Enthusiast

    Points: 129

    The numbers given to invoices entered by the users must be unique and also with not gaps . That means that sequences cannot be used since if a transaction rollbacks the number cannot be used by another user. What I actually want to achieve is when a user gets the next available number from the invoice types table (inside the transaction of a new invoice) , to ensure that no other user gets the same number until the transaction is commited. If the transaction is rolledback the number will be available for use by the next user.

    I just want to lock one row from the invoice type during the course of a transaction (so that no other user gets the same number specified in this row)  or any other way to achieve the same result.
    Thank you

  • george_at_sql

    SSCommitted

    Points: 1797

    If the Insert statement you are running comes from a single sql statement, then you wouldn’t need to manually lock any records?

    eg:

    BEGIN TRANSACTION [Tran1]
    BEGIN TRY
    Insert into invoice_table(id,invoice_company,col2,col3,....)
    select (select max(id)+1 from invoice_table) as id
            ,'Company XYZ' as invoice_company
            ,col2 
     
    COMMIT TRANSACTION [Tran1]
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1]
     PRINT 'Some error'
     THROW
    END CATCH

    However the requirement for gap free monotonically increasing numbers would mean you are limiting the scalability of the transaction, since, max+1 logic in a multi user scenario would mean that users would have to wait until the previous transaction is complete to get their inserts to work. This kind of problem can be overcome using sequences, if the requirement for gap free is not such a big deal. As it is by using a database if your actions are coded to be atomic(either the invoice module would cause an insert to the table or never gets into the database and throws an exception), the you can be sure that you wont have any missing invoices from your application when the application calls the “add_invoice” stored procedure..

  • Gail Shaw

    SSC Guru

    Points: 1004424

    subramaniam.chandrasekar - Friday, February 23, 2018 8:40 PM

    If I'm not wrong, the default isolation level of SQL Server 2014 is READ COMMITTED SNAPSHOT.

    The default isolation level for all versions of SQL Server is READ COMMITTED.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail Shaw

    SSC Guru

    Points: 1004424

    george-178499 - Saturday, February 24, 2018 9:40 AM

    If the Insert statement you are running comes from a single sql statement, then you wouldn't need to manually lock any records?

    eg:

    BEGIN TRANSACTION [Tran1]
    BEGIN TRY
    Insert into invoice_table(id,invoice_company,col2,col3,....)
    select (select max(id)+1 from invoice_table) as id
            ,'Company XYZ' as invoice_company
            ,col2 
     
    COMMIT TRANSACTION [Tran1]
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1]
     PRINT 'Some error'
     THROW
    END CATCH

    I’ve done a lot of testing, and that is not sufficient to prevent duplicates. You need at lease XLOCK and HOLDLOCK hints in the SELECT, and tbh, the only way I could get my test code to not deadlock or insert duplicates was to take a TABLOCKX in the select.

    p.s. and  putting a name on a Rollback is risky, can lead to errors that leave the transaction open after the batch ends.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ScottPletcher

    SSC Guru

    Points: 97741

    nickosl50 - Friday, February 23, 2018 11:17 PM

    The numbers given to invoices entered by the users must be unique and also with not gaps . That means that sequences cannot be used since if a transaction rollbacks the number cannot be used by another user. What I actually want to achieve is when a user gets the next available number from the invoice types table (inside the transaction of a new invoice) , to ensure that no other user gets the same number until the transaction is commited. If the transaction is rolledback the number will be available for use by the next user.

    I just want to lock one row from the invoice type during the course of a transaction (so that no other user gets the same number specified in this row)  or any other way to achieve the same result.
    Thank you

    That’s a huge effort to insure.  Do you really need that?  I understand if it’s some type of legal requirement, but not just to “not have gaps” because you don’t like gaps.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    ScottPletcher - Monday, February 26, 2018 7:50 AM

    That's a huge effort to insure. 

    And it pretty much requires that your process is serialised (one user at a time) to get no duplicates, no gaps and no deadlocks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply