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


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


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

Author
Message
nickosl50
nickosl50
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 25
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
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3664 Visits: 531
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
andrew gothard
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10868 Visits: 6700
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
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98129 Visits: 7246
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)
Smile Smile Smile
Health & Nutrition
nickosl50
nickosl50
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 25
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
Chris Harshman
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37941 Visits: 7164
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
Luis Cazares
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165923 Visits: 22823
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
ScottPletcher
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86937 Visits: 9729
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
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3664 Visits: 531
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
nickosl50
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 25
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
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