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?