HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Does blocking always mean open transaction?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
alwaysmeanopenblockingtransactiondoes

Problem

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation (database in SIMPLE mode)?

Solution

No.

If you try the following from two different connections then the second one will be blocked by the first (visible in sys.dm_os_waiting_tasks) but neither will result in any transaction log activity and running DBCC OPENTRAN will report "No active open transactions" (assuming no other activity).

SELECT COUNT_BIG(*)
FROM master..spt_values v1 WITH (TABLOCKX),
     master..spt_values v2 WITH (TABLOCKX),
     master..spt_values v3 WITH (TABLOCKX),
     master..spt_values v4 WITH (TABLOCKX)

Code Snippets

SELECT COUNT_BIG(*)
FROM master..spt_values v1 WITH (TABLOCKX),
     master..spt_values v2 WITH (TABLOCKX),
     master..spt_values v3 WITH (TABLOCKX),
     master..spt_values v4 WITH (TABLOCKX)

Context

StackExchange Database Administrators Q#8822, answer score: 7

Revisions (0)

No revisions yet.