Is it true that putting data into SQL Server locks the whole table?

entity-framework sql-server

Question

I'm putting entries into our database that include blob fields using Enterprise Framework. Up to 5 MB of data may be stored in the blob field.

Does it lock the whole table when a record is inserted into it?

As a result, if you query any data from the database, would it stall until the insert is complete (I realize there are workarounds, but I am talking about the default)?

How long until it results in a deadlock? Would the length of time depend on the server's load, for example, if there is little traffic, will it take longer to produce a deadlock?

Is it possible to keep track of what is locked at any given time?

Is there a scenario where blocking might happen if each thread is doing queries on a single table? So, is it true that only queries with joins and those working on several tables may cause a deadlock?

This assumes that the majority of my code consists of select statements and not a ton of lengthy transactions or anything of the kind.

1
49
11/28/2015 11:24:27 AM

Accepted Answer

You ask so many questions in this post, holy cow. Here are a few responses:

Does it lock the whole table when a record is inserted into it?

Not by default, but yes if you use the TABLOCK hint or if you're doing certain types of bulk load activities.

As a result, if you query any data from the database, would it stall until the insert is complete (I realize there are workarounds, but I am talking about the default)?

This becomes a bit more challenging. Yes, you will prevent someone if they attempt to choose data from a locked page in the table. You may get around this by using techniques like Read Committed Snapshot Isolation or the NOLOCK indication on a select statement. Check out The isolation levels poster by Kendra Little to get an idea of how isolation levels operate.

How long until it results in a deadlock? Will the amount of demand on the server at that moment affect how long it takes to reach a deadlock, for example, if there is little load?

Deadlocks are predicated on dependencies rather than on the passage of time. Consider the following scenario:

  • Query A is holding a lot of locks, and in order to complete his query, he needs things that Query B has locked.
  • Query B is holding a number of locks as well, and in order to complete his query, he requires items that Query A has locked.

When both queries become immobile (as in a Mexican standoff), SQL Server declares a draw, shoots one query in the back, unlocks his locks, and allows the other query to proceed. The victim is chosen by SQL Server depending on which one will cost less to roll back. If you want to get creative, you may paint targets on the back of certain queries using the SET DEADLOCK PRIORITY LOW setting, and SQL Server will fire those targets first.

Is it possible to keep track of what is locked at any given time?

Yes, you may query Dynamic Management Views (DMVs) like sys.dm tran locks, but using Free sp WhoIsActive stored procedure from Adam Machanic is the simplest method. It's a highly stylish alternative to sp who that you may refer to as:

sp_WhoIsActive @get_locks = 1

You'll get a little piece of XML that lists every lock that each executing query currently holds. You can tell who is blocking who by looking at the Blocking column. You need to look up the Online books that describe various lock kinds to comprehend the locks that are being kept.

Is there a scenario where blocking might happen if each thread is doing queries on a single table? So, is it true that only queries with joins and those working on several tables may cause a deadlock?

On a single table, queries may stall, believe it or not, A single query may even get stuck.. Visit Deadlocks: The Challenge by Jeremiah Peschka to get much additional information about deadlocks.

123
11/13/2018 11:10:04 PM

Popular Answer

You can impose row level locking if you have direct access to the SQL by using:

INSERT INTO MyTable(Id, BigColumn) WITH (ROWLOCK)
VALUES(...)

These two responses might be useful:

Is SQL Server capable of forcing row level locking?

Entity Framework table locking via a select

Look under the server, then under Management/Activity Monitor in Management Studio to see the locks that are currently being held. You may check to see whether the inserts are really an issue in its section for locks by object.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow