Re: Behavior of Connection.commit()
Angel Saenz-Badillos[MS] wrote:
Sam,
The problem is the same as above. If we set set_implicit_transactions on we
will no longer be able to tell that a serious issue happened on commit and
we would not throw an exception:
//start transaction
//insert data 1
//execute ddl
exception. At this time insert data 1 has been rolled back!
//insert data 2 //with set_implicit_transactions on this will create a NEW
transaction under the covers.
Understood. One other thing to consider is if the driver can know from
the DBMS message when (the rare case such as deadlock or DDL) when the
DBMS has already killed the whole tx, and in that case, throw an exception
from any subsequent connection, statement or result set method that would/could
do an update or query (because it may hold locks) (from this connection)
saying "The DBMS has killed the current transaction. No further DBMS
access is allowed until you call rollback()".
//COMMIT will no longer throw an exception, it will commit the transaction
started with insert data 2 and you will silently ignore the fact that data 1
has been rolled back.
Correct, but there is a problem as soon as the tx has been rolled back,
and there can be troubles even allowing the insert data 2 to proceed. The
user application may depend on holding locks in order, and once it is
allowed to proceed obtaining the lock for data 2, it may then go on to
obtain other locks before trying to commit, and this not-expected order
of locking (data 1 and any other data previously locked are now unlocked)
may cause deadlocks that could kill other innocent, correct transactions.
HTH,
Joe
.
Relevant Pages
- Re: Transaction problem (delete / select)
... I confirmed that the problem is the locks when using delete. ... When editing a student i can also delete, edit or insert in the grid ... The transaction begins before editing the student and ends when i don't need ... be visible to others users until i commit the transaction. ...
(microsoft.public.sqlserver.server) - Re: all about transaction
... The transaction isolation level is a setting that controls how long shared ... locks are held by a transaction and whether exclusive locks are honored. ... >both of us commit the transactions whatever the data i edited will get ... >commit tran ...
(microsoft.public.sqlserver.programming) - Re: Commit after transacton is completed
... But suppose if my transaction takes a few minutes ... Absolutely - it is a HUGE disadvantage to have readers scan the data as ... and any other user would read either the T1 data before commit or the T6 ... releasing locks to avoid reading ...
(comp.databases.oracle.server) - Re: ISOLATION levels and when to use SERIALIZEABLE
... the same at any isolation level. ... Please let me know if you can get that transaction to behave incorrectly. ... >> doesn't matter since they all use X locks. ... >> the transaction as short as possible and COMMIT asap anyway, ...
(microsoft.public.sqlserver.programming) - Re: Row-X (SX) lock on materialized view
... :>: refresh logs to check against. ... :> I have been asked to check why an application has locking issues, ... :> One key table typically has a dozen row locks (users editing in the main ... The commit merely means it ...
(comp.databases.oracle.misc) |
|
