November 17, 2010 at 6:36 am
Hi,
In my application the below procedure is called which will update the next integer value in the table that is passed to it. The table contins only 3 records. It is most used table as it is generating the next integer value for the 3 tables. But most of the time this table is getting locked. Can you please tell what to do so that it cannot be get into block state.
CREATE PROCEDURE [dbo].[GetNextKeyValue_p]
(
@TableNameVARCHAR(50)
,@KeyValueINT OUTPUT
,@Add2KeyValueINT = 1
)
AS
BEGIN TRAN T1
-- IF @Add2KeyValue is null SET @Add2KeyValue = 1
UPDATE dbo.NextKeyValue
SET KeyValue = KeyValue + @Add2KeyValue
WHERE TableName_PK = @TableName
SELECT
@KeyValue = KeyValue
FROM dbo.NextKeyValue
WHERE TableName_PK = @TableName
COMMIT TRAN T1
Regards,
Naveen
November 17, 2010 at 6:49 am
ekknaveen (11/17/2010)
Hi,In my application the below procedure is called which will update the next integer value in the table that is passed to it. The table contins only 3 records. It is most used table as it is generating the next integer value for the 3 tables. But most of the time this table is getting locked. Can you please tell what to do so that it cannot be get into block state.
CREATE PROCEDURE [dbo].[GetNextKeyValue_p]
(
@TableNameVARCHAR(50)
,@KeyValueINT OUTPUT
,@Add2KeyValueINT = 1
)
AS
BEGIN TRAN T1
-- IF @Add2KeyValue is null SET @Add2KeyValue = 1
UPDATE dbo.NextKeyValue
SET KeyValue = KeyValue + @Add2KeyValue
WHERE TableName_PK = @TableName
SELECT
@KeyValue = KeyValue
FROM dbo.NextKeyValue
WHERE TableName_PK = @TableName
COMMIT TRAN T1
Regards,
Naveen
Why cannot you use identity column for the three tables? You are entering a numeric value to the previous value which is acting as key.
If this procedure is called heavily, each process will try to acquire exclusive lock on the table.
try moving commit tran T1 between update and select. See if that makes any difference.
November 17, 2010 at 7:46 am
The best thing to do would be to switch to using an identity column.
If you must keep the current bad design:
1. Make one table for each table you are maintaining this way to minimize the impact of blocking.
2. Do not use a transaction with UPDATE and SELECT. Just return the new value from the UPDATE using the OUTPUT clause.
3. Make sure that you do not call this stored procedure inside a transaction.
November 18, 2010 at 5:18 am
Thanks for your information, I dont want to change the current desing. Keeping the current design is there any way sothat blockings can be eliminated. Is it good idea to have the commit tran just after the update statement, because anyhow the next select statement is returning the data from nextkeyvalue tabke where it contains only 3 records.
Give me with ex how we can eliminate the blockings.
Regards,
Naveen
November 18, 2010 at 5:20 am
try sql hints like with nolock or maxdop... if it can help
----------
Ashish
November 18, 2010 at 7:59 am
ekknaveen (11/18/2010)
Thanks for your information, I dont want to change the current desing. Keeping the current design is there any way sothat blockings can be eliminated. Is it good idea to have the commit tran just after the update statement, because anyhow the next select statement is returning the data from nextkeyvalue tabke where it contains only 3 records.Give me with ex how we can eliminate the blockings.
Regards,
Naveen
I already explained how to do it in my last post.
November 18, 2010 at 10:26 pm
when the application is tryting to call this procedure from the application, getting the below error:
failed to get next key value from stored procedure for table:Event
It looks like when something is processing and another try to process getting the above error. how to overcome with that type of error.
Regards,
Naveen
November 19, 2010 at 12:47 am
As sugested, its good to have identity column, but the design here is it is having a 2 table design. ie for events, there are 2 tables event_1 and event_2. if we make this as identity then both will be having the same numbers. is there any other way when we retrieve both should contain the unique numbers.
November 26, 2010 at 6:00 pm
You actually don't need an explicit transaction unless you want to roll it back. The idea is that the next key value in the table has the one you want to use and you update it for the next one. If you do it that way you shouldn't have any contention for the table.
Consider this:
UPDATE dbo.NextKeyValue
SET
@KeyValue = KeyValue
, KeyValue = KeyValue + @Add2KeyValue
WHERE TableName_PK = @TableName
@KeyValue has the value from the table, which is the next one to use. KeyValue in the table is updated for the next time it is needed. You don't have to do a select after the update. You already have the next one that you're going to use.
You don't need an explicit transaction since any update creates an implicit transaction anyhow.
There is a limit in the number of concurrent users that can get the next value, but I've seen this technique work on a very large Point Of Sale system to get sales tickets where there were over 20 stores each with 20 or so cash registers all ringing up sales.
I'm anxiously awaiting Paul White's new article on sequence tables so I can revise my techniques on this sort of thing.
Todd Fifield
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply