sql server 2008 transaction processing

I have 4 stored procedures that perform insert/update operations;  the first writes to a table with an identity column and returns the the identity of the new record.  that value is then used in the other 3 procedures.  All 4 are wrapped in a transaction.   The last procedure does a join on the new data that was created by the first procedure:
"select t1.value, t1.value2
 from mytable t2
 inner join myfirsttable t1 on t2.id = t1.id"
if all goes well the transaction is committed, otherwise rolled back.  My question is, where do these updates actually exist prior to the commit/rollback?  is the join in the last query valid, since the commit hasn't happened yet?  should the first query be removed from the transaction, since the data inserted into the table doesn't really exist until the commit?
dhenderson12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The data exists within the current transaction. It's only not available to the rest of connections until commit happens so it's ok like you have.
Just be sure that this is a fast process (at least less than 1 second) so it won't cause locks.
ste5anSenior DeveloperCommented:
Read more about the ACID properties (in opposite to BASE in No-SQL systems and also about CAP) and also about isolations levels.

The data exists in the table, but it is not "finalized". So you last query is valid.

Depending on the isolation levels of other sessions, they may also see your data (READ UNCOMMITED).
dhenderson12Author Commented:
so based on your answers, a rollback causes a "cascading" delete across the tables?
ste5anSenior DeveloperCommented:
SQL Server knows which rows are affected. So they are removed/undone in one logical operation. Not cascading like in cascading triggers or cascading foreign key relationships.
ZberteocCommented:
Think of it like versionning or branching, the part of the table that is affected by a transaction exists in 2 versions, 1 that is permanent, let's call it v1, and available outside the transaction and one that is temporary, let's call it v2, available only inside the transaction. If the transaction commits then v1 will be replaced by v2, which then becomes permanent and available to everyone. If transaction fails then v2 will be rolled back/discarded and v1 remains intact.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.