SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Displacement in table data ! please Help !
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stbox
Starting Member

6 Posts

Posted - 01/20/2013 :  19:06:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 01/20/2013 :  20:19:26  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/21/2013 :  01:26:08  Show Profile  Reply with Quote
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 - 01/23/2013 :  15:08:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 01/23/2013 :  15:30:13  Show Profile  Reply with Quote
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 - 01/29/2013 :  18:38:04  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/29/2013 :  23:41:20  Show Profile  Reply with Quote
how is your insert procedure?

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

Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
279 Posts

Posted - 01/30/2013 :  16:46:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000