CODEWITHSUNDEEP
sql-server
Erechtheus
Erechtheus

Reputation: 765

Do transactions in SQL Server lock all tables within the statement by default?

Suppose I have a T-SQL statement like so:

BEGIN TRAN

    UPDATE dbo.TableA 
    ...
    ...
    ...

    DELETE FROM dbo.TableB

COMMIT TRAN

Suppose that the update on TableA is going to take some time.

By default, would SQL Server lock TableB until the transaction is completed? Would that mean you can't read or write to it while the update is ongoing?

Upvotes: 6

Views: 4521

Answers (2)

Farhad Rahmanifard
Farhad Rahmanifard

Reputation: 688

Following the comments of @Jeroen Mostert, @marc_s, and @Cato under the question, your locks on TableA and TableB here are likely to escalate to table exclusive locks as there is no "where" clause. If so, the other read and write operations from different connections may be affected based on their transaction isolation level until the end of this transaction.

Besides, locks are created on-demand; it means that the query first puts a lock on the tableA and after the execution of the update operation, it puts another lock on the tableB.

Upvotes: 1

jean
jean

Reputation: 4350

Short answer: NO and NO.

Long answer: This is, in fact, a great question as it goes deep in transaction concepts and how the engine works but I guess a complete answer can occupy a good deal of a chapter on a good book and is out of the scope of this site.

First, keep in mind the engine can work in several isolation modes: snapshot, read committed, etc. I can recommend good research on this topic (this can take a few days).

Second, the engine has the granularity level and will try to use the "smallest" one but can escalate it on demand, depending on many factors, for example: "will this operation need a page split?"

Third, BEGIN, COMMIT, ROLLBACK work more in a "semaphore" way, flagging how changes are being phased from "memory" to "disk". It's a lot more complicated than it and that why I use quotes.

That said a "default transaction" will use a row granularity in a read committed isolation mode. Nothing says how locks will be issued one way or another. It depends on stuff like foreign keys, triggers, how much of the table is being changed, etc.

TLDR: It depends on a lot of minor details particular to your scenario. The best way to find out is by testing.

Upvotes: 1

Related Questions