July 3, 2017 at 12:44 pm
Hi,
I have made a stored procedure with the code below that generates and returns document number.
The database has a table with the folowing colums:
ID int (Unique identifier)
Year_ Int (current year)
nINV Int (stores the last emited invoice number)
and so on for all the documents that i need to print.
Closed Bit (means year is finish, needs to start another one)
Question is when i use the TABLOCKX, HOLDLOCK does it locks the datatable preventing other users in another computers from accessing it or only prevents other threads in my computer from accessing it?
Does it reases the lock when finish?
USE [gfbDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GET_INV_NUmber]
-- Add the parameters for the stored procedure here
@Ano INT,
@nINV INT OUTPUT
AS
BEGIN
SELECT @nINV=Invoice_N+1
FROM docNumbers
WITH (TABLOCKX, HOLDLOCK)
WHERE Year_ =@Ano
UPDATE docNumbers set Invoice_N=@nINV
END
Thanks
July 3, 2017 at 1:10 pm
HOLDLOCK is equivalent to SERIALIZABLE. TABLOCKX just makes the table level lock exclusive. Does that help? You can review these table hints here:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 1:36 pm
Hi,
Thanks for the reply.
So if i understand i would need to add a new parameter "@myIndex" and a lock like this:
WITH (TABLOCKX, ID(@myIndex), HOLDLOCK)
To get a lock on the row and garantie that no one else can modify it while i m runing the stored procedure.
July 3, 2017 at 2:48 pm
divinomestre79 - Monday, July 3, 2017 1:36 PMHi,
Thanks for the reply.
So if i understand i would need to add a new parameter "@myIndex" and a lock like this:
WITH (TABLOCKX, ID(@myIndex), HOLDLOCK)To get a lock on the row and garantie that no one else can modify it while i m runing the stored procedure.
That's not a lock on a row, that's an exclusive lock on the entire table. No one else will be able to read or modify any row in the table until your transaction has finished.
Index hints are not recommended, unless you know better than the optimiser what index is best for that query in all cases (and it can't, afaik, take parameters).
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
July 3, 2017 at 2:54 pm
Ok, thanks alot, that clarifies it.
Another question about this procedure, in the remote event of 2 diferent computers try to access this table at the same time will it wait for the first to finish and then execute the second request or will it rise an error?
July 5, 2017 at 10:49 am
Short answer:
To get the results you seek, replace the body of your stored procedure with a single step to update the value in the table and assign the result to your output variable:ALTER PROCEDURE dbo.GET_INV_NUmber
@Ano INT,
@nINV INT OUTPUT
AS
UPDATE dbo.docNumbers
SET @nINV = Invoice_N += 1
WHERE Year_ = @Ano;
RETURN;
(Sample/validation code at end of this post)
Breaking down the complex statement SET @nINV = Invoice_N += 1:
SET Invoice_N += 1 is the same as SET Invoice_N = Invoice_N + 1
Adding @nINV = in front of it means to assign the results of the update to the variable.
Additional thoughts:
1. Locks are held, at most, for the life of a transaction. When the transaction completes (commit or rollback), the locks are released. While a session attempts to acquire a lock on a resource with an incompatible lock on it (for example, attempting to read from a row that is locked by another user during an update), it will be blocked and simply wait until wither the lock is available or the client gives up, such as after an ADO-enforced timeout period. If you keep your transactions short and specific, you will benefit. This affects your proc as written:
A. There are no explicit transactions defined in your procedure. If the call to this proc isn't inside a transaction, then each statement within it is effectively a separate transaction. See inline comments./* Comments apply when NOT called from inside a transaction */
ALTER PROCEDURE [dbo].[GET_INV_NUmber]
@Ano INT,
@nINV INT OUTPUT
AS
BEGIN
/* If no transaction exists when this proc begins, then the following line is a single transaction */
SELECT @nINV=Invoice_N+1
FROM docNumbers
WITH (TABLOCKX, HOLDLOCK)
WHERE Year_ =@Ano
/* The HOLDLOCK hint will hold the exclusive table lock grabbed by TABLOCKX for the life of this transaction.
This transaction both begins and ends at this line, so the lock is immediately released before proceeding
to the UPDATE statement. */
/* When the next line begins, all locks have been released. It is possible for a separate session call to
this proc to claim the same Inv number at this point, before the update call. Unlikely, but possible.*/
UPDATE docNumbers set Invoice_N=@nINV
/* I'm guessing you also want to include a WHERE clause here (WHERE Year_ = @Ano) */
END
B. If a transaction was open when this proc is called (BEGIN TRAN... EXEC dbo.GET_INV_NUmber), then those locks will be held until the calling transaction is itself committed or rolled back./* calling the proc from inside a transaction will hold the table lock */
BEGIN TRANSACTION
/* do stuff... */
DECLARE @nINV int, @Ano int = 2017;
EXEC dbo.GET_INV_NUmber @Ano, @nINV OUTPUT;
/* TABLOCKX on dbo.docNumbers still held here - table remains locked */
EXEC [...] /*do stuff with results */
COMMIT TRANSACTION; /* locks finally released here */
This pattern shows up either accidentally or when there's a requirement that rolling back the insert should also roll back the ID on the docNumbers table in an effort to prevent unused numbers. Due to the length of time the locks would be held for such a requirement, such a pattern is usually something to avoid.
If the generation of these invoice numbers is high speed, then it is critical that this step perform quickly and release all locks.
Here's the sample code for the proc changes I suggested at the top of this post:
/* Set up sample objects and data */
CREATE TABLE dbo.docNumbers(Year_ INT NOT NULL PRIMARY KEY, Invoice_N INT NOT NULL);
GO
INSERT dbo.docNumbers(Year_, Invoice_N)
SELECT * FROM ( VALUES (2015, 100), (2016, 200), (2017, 50), (2018, 0)) AS v(Year_, Invoice_N);
GO
SELECT * FROM dbo.docNumbers;
GO
CREATE PROC dbo.GET_INV_NUmber(
@Ano INT,
@nINV INT OUTPUT
)
AS
SET NOCOUNT ON;
UPDATE dbo.docNumbers
SET @nINV = Invoice_N += 1
WHERE Year_ = @Ano;
GO
/* Validation code */
DECLARE @CurAno INT = 2017, @NewInvNum int;
SELECT 'Before call' AS [CurState], @NewInvNum AS [NewInvNum], * FROM dbo.docNumbers WHERE Year_ = @CurAno;
EXEC dbo.GET_INV_NUmber @CurAno, @NewInvNum OUTPUT;
SELECT 'After call' AS [CurState], @NewInvNum AS [NewInvNum], * FROM dbo.docNumbers WHERE Year_ = @CurAno;
Eddie Wuerch
MCM: SQL
July 24, 2017 at 12:16 pm
Hi, sorry for the late replay, but have been working on something else and also reading about this subject.
If i uderstud correctly what i have been reading when the "UPDATE" is called it places a UPDATE LOCK but thats a shared lock, correct?
So i did a little more research and i m now trying this:
USE [GFD_DB]
GO
/****** Object: StoredProcedure [dbo].[ORC_NB] Script Date: 24/07/2017 20:14:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ORC_NB]
@CurAno INT,
@ORCN int OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SET NOCOUNT ON;
UPDATE dbo.Numerizar
SET @ORCN = ORCM += 1
WHERE AN = @CurAno;
COMMIT TRAN
Is this a better way of getting it done?
July 24, 2017 at 12:42 pm
divinomestre79 - Monday, July 24, 2017 12:16 PMHi, sorry for the late replay, but have been working on something else and also reading about this subject.
If i uderstud correctly what i have been reading when the "UPDATE" is called it places a UPDATE LOCK but thats a shared lock, correct?
No, it's a UP lock, not SH. Other sessions can read the row, can't be modified by another session. Will be converted to an exclusive lock before the modification is done.
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
July 24, 2017 at 12:50 pm
Right, but when i use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE i get exclusive lock on the table for the all TRANSACTION, correct?
July 24, 2017 at 1:19 pm
No, serialisable doesn't force exclusive locks or table-level locks.
The isolation levels mostly change how read queries behave, not data modifications
You get a upd lock, on whatever resource SQL thinks is best (row, page or table) that's converted to exclusive to perform the update, that exclusive is then held until the end of the transaction. This is true in any isolation level.
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
July 24, 2017 at 1:33 pm
Ok, in that case i need help...
Is it possible to get a exclusive lock on that table at the begining at that TRANSACTION?
And how?
This stored procedure is called from VB NET to get the document number, either a invoice or any other, i havent tested the last one, i tested the one before that was sugested to me.
I had 2 computers in calling the procedure in a loop like this, using a timer, so every fraction of a second till a certain number of invoices was reached.
At 1000, no problem, at 10 000 invoices some numbers were missing, at 100 000, were missing about 1300 invoice numbers.
Well, its true that this software will never be in a eviroment that as 100 000 invoices in even a year, but it showed me that i was doing something wrong.
July 24, 2017 at 1:42 pm
divinomestre79 - Monday, July 24, 2017 1:33 PMOk, in that case i need help...
Is it possible to get a exclusive lock on that table at the begining at that TRANSACTION?
And how?
Sure. The way you were doing it in the very first post in this thread.
divinomestre79 - Monday, July 3, 2017 12:44 PM
WITH (TABLOCKX, HOLDLOCK)
Put that into the first statement that references the table in that transaction.
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
July 24, 2017 at 1:50 pm
Like this then:
USE [GFD_DB]
GO
/****** Object: StoredProcedure [dbo].[ORC_N] Script Date: 24/07/2017 21:46:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ORC_N]
@CurAno INT,
@ORCN int OUTPUT
AS
BEGIN TRAN
SET NOCOUNT ON;
UPDATE dbo.Numerizar WITH(TABLOCKX, HOLDLOCK)
SET @ORCN = ORCM += 1
WHERE AN = @CurAno;
COMMIT TRAN
July 24, 2017 at 2:00 pm
You don't really need the explicit transaction. A single statement is its own transaction, and since there's no error handling, there's no real gain from explicitly declaring a transaction.
Keep in mind that an exclusive table lock can have serious negative impact on performance. Make sure your indexing is good and that your query runs as fast as possible.
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
July 24, 2017 at 2:01 pm
You can remove the begin/commit tran – as there is only one statement there, it's an implicit transaction anyway.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply