February 23, 2018 at 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
February 23, 2018 at 1:53 am
nickosl50 - Friday, February 23, 2018 1:12 AMI 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.
February 23, 2018 at 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'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 23, 2018 at 6:35 am
andrew gothard - Friday, February 23, 2018 3:13 AMHi,
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 startcheers
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 23, 2018 at 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
February 23, 2018 at 11:01 am
nickosl50 - Friday, February 23, 2018 10:30 AMI 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
February 23, 2018 at 11:24 am
Chris Harshman - Friday, February 23, 2018 11:01 AMnickosl50 - Friday, February 23, 2018 10:30 AMI 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;
February 23, 2018 at 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.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 23, 2018 at 8:40 pm
ScottPletcher - Friday, February 23, 2018 12:51 PMYes, 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.
February 23, 2018 at 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
February 24, 2018 at 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
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..
February 26, 2018 at 6:33 am
subramaniam.chandrasekar - Friday, February 23, 2018 8:40 PMIf 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
February 26, 2018 at 6:37 am
george-178499 - Saturday, February 24, 2018 9:40 AMIf 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
February 26, 2018 at 7:50 am
nickosl50 - Friday, February 23, 2018 11:17 PMThe 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 26, 2018 at 7:53 am
ScottPletcher - Monday, February 26, 2018 7:50 AMThat'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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply