By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Strange result using transactions

P: 2
I am executing the following queries:

1) begin;
1) delete from forum where id = 20;
1) insert into forum (id, name) values (20, 'test');
2) delete from forum where id = 20;
1) commit;

Queries marked with 1) are executed by process 1 and queries marked with 2) are executed by process 2.

The problem is that process 2 gets the message 'DELETE 0'. I would expect him to get the message 'DELETE 1'. Can anyone explain this behavious?

id has a unique key.
Mar 25 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 534
What you see is the correct behavior; process 2 cannot see the fresh record inserted by process 1 because the transaction initiated by process 1 has not been committed yet.

See these links for more info:
Transactions

Transaction isolation

Set Transaction
Mar 26 '07 #2

P: 2
Thank you for your reply.

The strange thing is that i also get the result when there is already an row with id=20 in the database. Why does process sees the uncommitted DELETE, but doesn't see the INSERT?
Mar 26 '07 #3

Expert 100+
P: 534
Just to make sure that we are on the same page - could you visualize for me this second scenario with step-by-step actions as you did for your first case?
Mar 27 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.