Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
stbox
Starting Member
6 Posts |
Posted - 2013-01-20 : 19:06:00
|
hi first sorry for bad english!please help me ! i have a big problem ! i write a multi storid procedure in sql database for my application!my application is a program that work in network!Sometimes a function is called by two person or when a multi function run ! The value of variables used in the function of one seasion use to another user in another seasioni do not run concurrently the storid function ?or probebly my code is bad !for example :----------------------------exec @payid=insertpay @sprice,1,0,@UserName,@uid,0;insert into User_Charge_His(Reason,[Date],[Time],Sid,UserSeasionId,UserName,payid,ChargeSize,ChargeDays,DateFrom,DateTo) values (1,@datesabt,@timesabt,@sid,@uid,@UserName,@payid,@trafic,@days,@UserPersianStartDate,@UserPersianExpiredate) -------------------------------------after get a payid , payid use in insert to table User_Charge_His for a user but i see payid Recorded to other user !The time saved for each user is 12:10:11It is possible to run concurrently?What should I do? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-20 : 20:19:26
|
As you suggest, this is likely a concurrency issue. But, the stored procedure "insertpay" that generates the value of @payid is part of the problem. Look at the code for the stored procedure to see why it is generating duplicate id's. It may very well be possible that the stored procedure is looking into the User_Charge_Hits table to see what the max payid is and picking the next value. If that is the case, you would need to do the two steps (of getting the id via selection and then inserting the id) in a transaction and get a mutually exclusive lock (UPDATE or EXCLUSIVE) when selecting. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 01:26:08
|
are you doing this inside a transaction? whats the isolation level used?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
stbox
Starting Member
6 Posts |
Posted - 2013-01-23 : 15:08:23
|
hithis is PROCEDURE insert pay !Is there something wrong?use a scope_identity() is wrong ?ALTER PROCEDURE [dbo].[InsertPay](@Price numeric(10, 0),@Status int,@paynumber nvarchar(50),@username nvarchar(30),@uid int,@waypays int)as DECLARE @backpayid numeric(10,0)BEGIN declare @timesabt varchar(8),@tempdate datetime,@datesabt varchar(10) set @timesabt=CONVERT(VARCHAR(8),GETDATE(),108) set @tempdate=GETDATE() exec @datesabt=getShamsiDate @tempdate insert into pays([Status],Price,PayNumber,Date,Time,UserName,uid,waypays) values(@Status,@Price,@PayNumber,@datesabt,@timesabt,@username,@uid,@waypays) select @backpayid= scope_identity(); return @backpayid;END------I did not use the isolation! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-23 : 15:30:13
|
This seems fine to me - scope_identity is safe to use because it would return the identity value inserted in the previous statement even if you had other sessions inserting data. So it must be something else that is causing the incorrect ID's to show up in the calling program.The return type from a stored procedure is of type INT. You are using NUMERIC(10,0) - which should be fine.Are you certain that the incorrect ID is being associated with a given session? How did you come to that conclusion? |
|
|
stbox
Starting Member
6 Posts |
Posted - 2013-01-29 : 18:38:04
|
i insert a price value in two table !table1= price,username,...table2= backpayid , username,....I saw the difference between the value of importedusername id difference ! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 23:41:20
|
how is your insert procedure?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-01-30 : 16:46:05
|
I'd worry about the "pays" table not being fully qualified: is it possible you have "pays" tables under different schema names?Also, the obvious, verify that there actually is an IDENTITY column on the "pays" table . |
|
|
|
|
|
|
|