transactions

"Surabhi Ahuja " <surabhi.ahuja@iiitb.ac.in> writes:

Hello everyone.
I have the following question:

say i have two transactions going on simultaneoulsy.
in one transaction i do an insertion
and the other transaction i do a delete.
what will happen if in both the transactions, the insert(in one) and
delete(in the other) are issued at exactly the same time? will bothe get
blocked and enter into a deadlock, if yes then do both the transactions
get aborted after some time of waiting.

The transactions will be executed completely independently, unless you
do explicit locking (via SELECT FOR UPDATE or LOCK TABLE).

-Doug

On Mon, Jun 06, 2005 at 09:21:15AM -0400, Douglas McNaught wrote:

"Surabhi Ahuja " <surabhi.ahuja@iiitb.ac.in> writes:

say i have two transactions going on simultaneoulsy.
in one transaction i do an insertion
and the other transaction i do a delete.
what will happen if in both the transactions, the insert(in one) and
delete(in the other) are issued at exactly the same time? will bothe get
blocked and enter into a deadlock, if yes then do both the transactions
get aborted after some time of waiting.

The transactions will be executed completely independently, unless you
do explicit locking (via SELECT FOR UPDATE or LOCK TABLE).

Deadlock can still happen if Transaction A's INSERT blocks because
of something Transaction B did previously, and Transaction B's
DELETE blocks because of something Transaction A did previously.
Foreign key relationships are a typical cause, but even UPDATEs on
simple tables can cause later operations to deadlock if the application
isn't careful with its order of operations.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/