Обсуждение: Connecting to an existing transaction state.

Hello,

I’m in the process of creating a set of scripts for testing certain locking features in an application.
What I would like to do:
1. Start a connection from machine-01 through the m01-s1.sql script.
2.While (1) is running, start another transaction on the same database from machine-02 using m02-s1.sql.

At this point in time, there are two open transactions on certain tables in the same database.

3. Using m01-s2.sql I would like to execute a certain SQL statement – BUT within the scope of the transaction begun by
m01-s1.sql.
4. Current situation: Since there are several .sql scripts, each getting its own connection and executing sql stmts –
theyare not aware of activities of the other scripts (i.e. the open transactions). 
5. What I’d like to do: After a transaction has been started from a machine, I should be able to save the transaction
reference(id?) temporarily somewhere. 
6. The next statement (new .sql file) that wishes to execute within the scope of the above transaction – should be able
toget the transaction reference (id) and latch onto it in its current state. This way it continues to perform as part
ofa whole – rather than only executing the statements that it had. 

Any guidance in this will help.

Cheers!
AlexiG




      __________________________________________________________
Not happy with your email address?.
Get the one you really want - millions of new email addresses available now at Yahoo!
http://uk.docs.yahoo.com/ymail/new.html

On 10:37 am 07/28/08 Alex Gen <alexigen@yahoo.com> wrote:
> 3. Using m01-s2.sql I would like to execute a certain SQL statement
> – BUT within the scope of the transaction begun by m01-s1.sql.

I believe that is not possible.
Specially if you are within a transaction on each of the scripts.

As far as I know all the work getting done inside a transaction is ONLY
visible to that transaction. It would actually be pretty bad if the
database allowed a process to see an uncommited state from a transaction
that may end up rolling back.

What are you trying to test?
You mentioned how you are testing, but what is the business need? Or
business concern?

ie It could be something like.. we are concerned that if we run these two
scripts one may lock the other.