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 |
|
Aerathi
Starting Member
14 Posts |
 Posted - 2006-09-06 : 17:44:31
|
| I have kind of a weird question that I'm not even sure how to ask, so I will just give you a run down of what's happening. A while back I had to write a stored procedure that does some random processing. It was wrapped in a transaction, and at one point it inserts a record into a table. Later it calls another stored procedure which in turn looks up the data that was inserted into that same table earlier. If nothing there it processes one way, if there's something there it processes another. Everything worked fine when I wrote it, and by putting the flag in that inserted record it processed fine either way.Sometime recently other dba's went through and redid all of the stored procedures to make them more efficient, especially adding more transaction and error handling. Now that record gets inserted fine, everything processes in the same order but the deeper sproc that does the lookup processes as if that record doesn't exist.I am wondering if this is due to some kind of scoping issue or something with transaction handling that I am not familiar with or something along those lines. Or it could just be that somewhere down the line, something else was changed in another procedure and I just haven't found it yet. I just wanted to run it past everyone here to see if there's some kind of scoping type thing that happens that may be the cause of this. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-09-07 : 00:01:09
|
| It does sound like whatever operation you were performing is no longer atomic (or never was and now is). They could have also diddled with the transaction isolation level. Of course it could just be a bug introduced during the improvements.One question though - if the top procedure always inserts into the table why the check later, or is this just code reuse at work? |
 |
|
|
Aerathi
Starting Member
14 Posts |
Posted - 2006-09-07 : 10:21:28
|
| The top procedure inserts the record that basically has option bits for the order. Later on an existing stored procedure is called, that checks the flags in that record and those flags determine how rates are calculated, discounts, etc. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-07 : 10:35:55
|
| >>...to see if there's some kind of scoping type thing that happens that may be the cause of this.I'm guessing that it's a bug introduced during the improvements (as LoztInSpace offers as a possibility).As long as your (inner called) procedures are called ultimately from the master procedure, the uncommitted changes should be in scope to the called procedures. Now if a different sql "process" (somehow seperate from the master procedure and it's called procedures) is trying to read the transactions prior to the COMMIT then with a default isolation level they would be "out of scope".Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Transactions, Error Handling & Lookup Data