New answers tagged transaction
1
5 million rows is a lot, theoretically it would cause shared lock escalation for TableA to table-level lock in any case (explicit or implicit transaction)
But you can find this out experimentally, you just need to setup Extended Events session and monitor for "lock_escalation" event
Then run your query in explicit and implicit transaction and check out XE ...
1
In this specific example, because it includes a SELECT...INTO statement, I think it's fair to say that yes, there is a difference in the locking behavior.
Source Table (and metadata tables)
The SELECT...INTO statement is not an "atomic statement," it runs in two parts: creating the new table, and then performing the SELECT and INSERT portion of the query.
...
1
For SQL Server:
But what's about single statements?
In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that ...
8
For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be affected by a ROLLBACK (though technically both are somewhat "cheating" in a sense):
DML statements against table variables (variable, even table variables, do not ...
1
I've just run some tests, here are results:
Drop Database cannot be performed within explicit (begin tran...) transaction
SQL Server throws:
DROP DATABASE statement cannot be used inside a user transaction.
Drop table you can do it within explicit transaction without any problem
Create View - you can do it, but first have to run begin tran..., then run ...
2
is it true that all data modifications are undone when the transaction they are a part of
gets rolled back?
Yes. That is the WHOLE sense of a transaction.
For example if a cursor that just executed a stored procedure 100 times that updated a
table on each iteration, would all those updates get rolled back?
That is the definition of all changes.
...
4
It depends on the transaction isolation level.
In read committed it will release locks.
In "Repeatable read" and "Serializable" it will hold locks.
You can find more info in the following article: SET TRANSACTION ISOLATION LEVEL
Top 50 recent answers are included
