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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Displacement in table data ! please Help !

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 seasion

i 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:11

It 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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

stbox
Starting Member

6 Posts

Posted - 2013-01-23 : 15:08:23
hi

this 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!

Go to Top of Page

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?
Go to Top of Page

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 imported
username id difference !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 23:41:20
how is your insert procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 .
Go to Top of Page
   

- Advertisement -