June 16, 2015 at 12:12 pm
Hi all,
I do not want to confuse people, especially when they are trying o help me.
Below is a sample of how stored procedure seems :
PROC1
-----Procedure1 is calling with @StepId ,@Status Parameters-----
BEGIN TRY
BEGIN TRANSACTION
Insert Into Table1 (StepId,Status) values (@StepId ,@Status )
update Table1 U
set u.RefRecId = f.RecId
from Table1 u
inner join FlowHistorty f on
u.StepId = f.id
where u.StepId=@StepId
COMMIT TRANSACTION
EXEC PROC2 @StepId
END TRY
......
PROC2
-----Procedure2 is calling with @StepId Parameters-----
select * from Table3 inner join Table1 on Table3.refrecid=Table1.refrecid
where Table1.StepId =@StepId
......
Here, some times, select statement does not return anything. It seems that cannot find RefRecId=@recid. Procedure1 may be run from several users at the same time.
Thank you,
June 16, 2015 at 12:17 pm
You're doing an inner join with Table3. Are you sure that Table3 has the rows needed?
June 16, 2015 at 12:19 pm
Yes, table3 has refrecid row.
June 16, 2015 at 1:15 pm
j.grimanis (6/16/2015)
Is there a possibility that rows are still in commit process (multiple users call the first procedure ) and when the second procedure is running, the respective tables have not the updated data?
No.
SQL statements always run consecutively. The second procedure won't start until the first one has completed.
Can you post the entire procedures, not just a sample. Fair chance the problem is in the portion you didn't post.
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
June 16, 2015 at 2:51 pm
Because the second procedure is running inside the first one, after commit trans, first all transactions will be commited and after that will the second procedure be executed? I am afraid that it is executed during the commit, thats why it cannot read the updated table..... i will try to post the code tomorrow.
June 17, 2015 at 3:19 am
j.grimanis (6/16/2015)
I am afraid that it is executed during the commit, thats why it cannot read the updated table
That is not possible.
Statements run sequentially. If you have a call to a procedure after a COMMIT TRAN, the the COMMIT TRAN must start and complete before the procedure is called.
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
June 17, 2015 at 4:41 am
I think it's time to check your previous assumptions about what's succeeding and what's not. Try it in a test environment and check for transaction handling in your called procedures. Figure out what's REALLY going on. Like Gail said, statements are sequential, so the state of the data for a statement includes the affects of previous statements, so there must be something you're not seeing.
June 17, 2015 at 7:45 am
j.grimanis (6/16/2015)
Hi all,I do not want to confuse people, especially when they are trying o help me.
Below is a sample of how stored procedure seems :
PROC1
-----Procedure1 is calling with @StepId ,@Status Parameters-----
BEGIN TRY
BEGIN TRANSACTION
Insert Into Table1 (StepId,Status) values (@StepId ,@Status )
update Table1 U
set u.RefRecId = f.RecId
from Table1 u
inner join FlowHistorty f on
u.StepId = f.id
where u.StepId=@StepId
COMMIT TRANSACTION
EXEC PROC2 @StepId
END TRY
......
PROC2
-----Procedure2 is calling with @StepId Parameters-----
select * from Table3 inner join Table1 on Table3.refrecid=Table1.refrecid
where Table1.StepId =@StepId
......
Here, some times, select statement does not return anything. It seems that cannot find RefRecId=@recid. Procedure1 may be run from several users at the same time.
Thank you,
You have a TRY/CATCH block (although you haven't given the CATCH part). It seems fairly obvious that the TRY block is throwing an exception and the CATCH block is "handling" it. Your CATCH block should be logging or returning the error message, so that you can troubleshoot. You need to look at your CATCH block to figure out what it is doing when an exception is thrown.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply