April 15, 2011 at 1:49 pm
I have a user defined function with the following definition:
ALTER FUNCTION [dbo].[udfSelNextBoxNumByDate]
(
@pdtDateSMALLDATETIME
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @vintBoxNum SMALLINT
SELECT@vintBoxNum = ISNULL(MAX([dbo].[tblVerifications].[intBoxNum]),0) + 1
FROM[dbo].[tblVerifications]
INNER JOIN[dbo].[tblOrders] ON [dbo].[tblVerifications].[intOrder_ID] = [dbo].[tblOrders].[intOrder_ID]
WHERE[dbo].[tblOrders].[dtOrderDate] = @pdtDate
RETURN @vintBoxNum
END
Basically this returns the next available box number. But it depends on dates in the joined table tblOrders.
Is it possible to modify this function so that only one person at a time can read this next number? without locking the table tblOrders?
There is an SP that calls this function which gets the next box number and immediately inserts it into the table tblVerifications:
SELECT @pintNoBoite = dbo.udfSelNextBoxNumByDate(dtDateComE)
FROMdbo.tblOrders
WHEREintOrder_ID = @pintOrder_ID
-- Insert
INSERT INTO dbo.tblVerifications (blah, blah)
VALUES (blah, blah)
So, in summary, the user reads the next number and immediately inserts it in tblVerifications. This action will automatically make it ready for the next user to get the next box number.
But the problem is that if two people read at the same time, the same box number will be inserted into tblVerifications twice. There has to be a lock somewhere for the table tblVerifications but I don't want to lock tblOrders because that table is always being updated by many other users.
April 16, 2011 at 1:28 am
Either you can convert your boxnumber to an identity column and get the inserted row by SCOPE_IDENTITY() function or you create an extra table to save the last returned boxnumbers.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 16, 2011 at 1:28 am
Either you can convert your boxnumber to an identity column and get the inserted row by SCOPE_IDENTITY() function or you create an extra table to save the last returned boxnumbers.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 17, 2011 at 1:24 pm
But that wouldn't fix the problem. I still have to go to this new table to get the next box number. And of course, two people can read the same number at the same time. I need to make sure no two people get the same box number when they request a new number at the same time. This is the problem I have with my query above. Two processes can read the next number at the same time.
April 17, 2011 at 3:59 pm
Scope_identity() would fix it, but if you use a second table you can start a tran with a select with a lock hint to prevent other user to get a new id and this would also fix it.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 18, 2011 at 10:35 am
Why don't you just replace the function with a procedure that gets the next box# and updates it in one shot? You don't need to join to the tblOrders table since you could pass the intOrder_ID (and avoid locking it).
CREATE PROCEDURE Get_Next_BoxNum( @OrderID INT)
as
SET NOCOUNT ON
DECLARE @vintBoxNum SMALLINT
BEGIN TRANSACTION
SELECT@vintBoxNum = ISNULL(MAX([dbo].[tblVerifications].[intBoxNum]),0) + 1
FROM [dbo].[tblVerifications]
WHEREintOrder_ID = @OrderID
INSERT INTO dbo.tblVerifications (blah, blah)
VALUES (@OrderID, @vintBoxNum)
COMMIT TRANSACTION
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 19, 2011 at 9:20 am
I need to JOIN it with tblOrders because the next box number is based on the order date. Box numbers can repeat on different days. If I wrap it inside a transaction, like you suggest, will this block other processes from reading/updating tblOrders?
April 19, 2011 at 10:10 am
Your UDF is based on getting the next box number of that day's orders. So I'm saying don't join tblOrders to avoid locking it and instead pass the procedure the OrderID you would use anyway. Consider:
declare @OrderID int;
set @OrderID = (
select top 1 intOrder_ID
from tblOrders
where dtOrderDate = @pdtDate
order by dtOrderDate desc);
exec Get_Next_BoxNum (@OrderID);
Where Get_Next_BoxNum is a procedure like I listed in my last post.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 19, 2011 at 10:41 am
CREATE SEQUENCE in SQL??? Care to catch me up Joe?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply