CODEWITHSUNDEEP
.netsqlsql-serverlockingtransactionscope
Eric J. Price
Eric J. Price

Reputation: 2785

Does the first insert into a table effectively lock the entire table?

Scenario:
TransactionScope(TransactionScopeOption.Suppress) query embedded within a wrapper TransactionScope(TransactionScopeOption.Required). Prior to the "Suppress" query there is another query associated with the wrapper "Required" TransactionScope.

These two queries only overlap on one table. The prior "Required" query Inserts into the table and the latter "Suppress" query Selects from the table. Ignore how stupid this is and the fact that the "Suppress" query will only read the unlocked rows and therefore will not include the newly inserted results.

Behavior:
The behavior I'm seeing is that if there are rows within the table the Insert will succeed and then the Select will read the unlocked rows and then move on. However, if the table is empty the Insert will succeed, but then the Select will get stuck as being blocked by the Insert.

Question:
What I'm trying to get is a conceptual understanding of how the locking is being tracked in this scenario. I used to think of table locking as being a kind of bit toggle where the table was either locked or unlocked, but it seems that it is more like a CASE statement with a NOT EXISTS; in the absence of an unlocked row the table is locked as opposed to the table is unlocked, but every row in it is currently in a locked state.

Everything that I've been able to find indicates that there are indeed different levels of locking (table, row, page, etc), but I can't find anything that states in explicit terms that if a table is empty and row locks exist on data being inserted then a table lock is implied. Originally I would've expected the Select to simply return a Null, but since this isn't the behavior I figured I'd reach out to the experts and see if someone can give me a definitive understanding.

Notes:
RDBMS: SQL Server 2012
.NET Framework: 4.0
Core Behavior: If a record exists in the table prior to the Insert then both queries succeed, but if the table is empty prior to the Insert then the Select gets blocked by the Insert.

Table:

Create Table Random(ID Int Identity, Word Varchar(50), TimeInserted DateTime)

Insert Query:

Insert Random(Word, TimeInserted)
Select 'Word', GetDate()

Select Query:

Select Max(TimeInserted)
From Random
Where Word Like 'A%'

Upvotes: 2

Views: 1000

Answers (1)

Guish
Guish

Reputation: 5160

The default IsolationLevel of TransactionScope is "Serializable". According to Msdn, in this isolation level "Volatile data can be read but not modified, and no new data can be added during the transaction". "Serializable" is very deadlock-prone.

The default IsolationLevel of an SQLTransaction is ReadCommitted. Try declaring your transaction scope like this

using (var scope = new System.Transactions.TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted, Timeout = TransactionManager.MaximumTimeout}))

It may solve your problem.

More on MSDN:

The highest isolation level, Serializable, provides a high degree of protection against interruptive transactions, but requires that each transaction complete before any other transactions are allowed to operate on the data.

Upvotes: 1

Related Questions