August 24, 2009 at 1:48 pm
We're having an issue building table which is used for keeping stock movements. Each movement of the stock is logged in a movement table which holds a datetime & a stock amount for each record. So when inserting a new movement (for example + 50) then the procedure should/is as follows :
* Get the latest stock by sorting the movement table by datetime desc
* insert new record with the stock from the last record + the new stock
These 2 actions are combined in a stored procedure which includes a insert select statement :
insert into test
(
testTIMESTAMP,
testVALUE
)
select
top 1
@newTimeStamp,
testValue + newValue
from test
order by
testtimestamp desc
The expected behaviour after executing the procedure would be that the test table contains incrementing testvalues, but when the procedure is executed multiple times from different connections the testvalues seem to contain duplicate values. Anyone got a solution for this problem ?
The following is a script to create the procedure & test table. To simulate the problem the spTest procedure should be executed like 500 times from 2 different connections at the same time.
/****** Object: Table [dbo].[test] Script Date: 08/24/2009 21:47:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[testID] [int] IDENTITY(1,1) NOT NULL,
[testVALUE] [int] NOT NULL,
[testTIMESTAMP] [datetime] NOT NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[testID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_test] UNIQUE NONCLUSTERED
(
[testTIMESTAMP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
CREATE procedure [dbo].[spTest]
as
begin
begin transaction
declare @today datetime = getdate();
retry:
begin try
insert into test
(
testVALUE,
testTIMESTAMP
)
select
top 1
testValue + 1,
@today
from
test with(tablock,xlock)
order by
testTIMESTAMP desc
if (@@ROWCOUNT = 0)
begin
insert into test
(
testVALUE,
testTIMESTAMP
)
values
(
0,
@today
)
end
end try
begin catch
print error_message()
set @today = @today + 0.000005
goto retry;
end catch
commit
end
GO
August 24, 2009 at 2:06 pm
Could you post some sample data and expected results? This would help figure out the problem.
August 24, 2009 at 2:19 pm
Okay ... here is the sample data :
The following is a sample of what we get when the procedure is ran from 2 different locations at the same time :
testID | testValue | testTimeStamp
14571222009-08-24 21:15:25.243
14551222009-08-24 21:15:25.240
14511222009-08-24 21:15:25.233
13021202009-08-24 21:15:25.230
14431212009-08-24 21:15:25.200
14381212009-08-24 21:15:25.190
14341212009-08-24 21:15:25.180
14301212009-08-24 21:15:25.170
14281212009-08-24 21:15:25.163
As you can see the testValue does not increment though the procedure always does a + 1 .... it should look like the following :
testID | testValue | testTimeStamp
14571202009-08-24 21:15:25.240
14581212009-08-24 21:15:25.241
14591222009-08-24 21:15:25.242
14501232009-08-24 21:15:25.243
14511242009-08-24 21:15:25.244
14521252009-08-24 21:15:25.245
14531262009-08-24 21:15:25.246
14541272009-08-24 21:15:25.247
14551282009-08-24 21:15:25.248
August 24, 2009 at 2:35 pm
Okay, that is what it IS doing. I am interested more in what it is SUPPOSED to do. To see that we need sample data for the table, and expected results. This should be based on the requirements stated in the original post.
Give us a starting point, then show what should happen as various movements are recorded.
August 24, 2009 at 2:49 pm
Okay I'll try again 🙂
So starting off with an empty test table and executing the procedure for the first time will result in the following row being inserted in the test table :
testID | testValue | testTimeStamp
1 1 2008-08-24 22:44:00
When the procedure is executed for the 2nd time the it would search for the last stock by selecting the first record after sorting the table on the testtimestamp field ... so the table after inserting the record will look like the following :
testID | testValue | testTimeStampe
1 1 2008-08-24 22:44:00
2 2 2008-08-24 22:44:01
The 3th time :
testID | testValue | testTimeStamp
1 1 2008-08-24 22:44:00
2 2 2008-08-24 22:44:01
3 3 2008-08-24 22:44:02
... and on ... every time the procedure is inserted it will insert a new record using the value of the last record (ordered by the testTimeStamp field) and incrementing it with 1 or more ...
Hope this will explain how it should work.
August 24, 2009 at 2:58 pm
jan.tilburgh (8/24/2009)
insert into test
(
testTIMESTAMP,
testVALUE
)
select
@newTimeStamp,
testValue + newValue
from test
order by
testtimestamp desc
If there are 4 rows in test at the point where that insert runs, how many rows should it insert? Currently it will insert 4, since there's nothing limiting the rows returned in the select.
If it's supposed to insert 1, you need to add TOP (1)
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
August 24, 2009 at 3:05 pm
You're correct it should insert only 1 record ! I forgot to add the top 1 in the example but it is included in the sql script.
GilaMonster (8/24/2009)
jan.tilburgh (8/24/2009)
insert into test
(
testTIMESTAMP,
testVALUE
)
select
@newTimeStamp,
testValue + newValue
from test
order by
testtimestamp desc
If there are 4 rows in test at the point where that insert runs, how many rows should it insert? Currently it will insert 4, since there's nothing limiting the rows returned in the select.
If it's supposed to insert 1, you need to add TOP (1)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply