Does inserting data into SQL Server lock the whole table?

I am using Entity Framework, and I am inserting records into our database which include a blob field. The blob field can be up to 5 MB of data.

When inserting a record into this table, does it lock the whole table?

So if you are querying any data from the table, will it block until the insert is done (I realise there are ways around this, but I am talking by default)?

How long will it take before it causes a deadlock? Will that time depend on how much load is on the server, e.g. if there is not much load, will it take longer to cause a deadlock?

Is there a way to monitor and see what is locked at any particular time?

If each thread is doing queries on single tables, is there then a case where blocking can occur? So isn't it the case that a deadlock can only occur if you have a query which has a join and is acting on multiple tables?

This is taking into account that most of my code is just a bunch of select statements, not heaps of long running transactions or anything like that.

Holy cow, you've got a lot of questions in here, heh. Here's a few answers:

When inserting a record into this table, does it lock the whole table?

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

So if you are querying any data from the table will it block until the insert is done (I realise there are ways around this, but I am talking by default)?

This one gets a little trickier. If someone's trying to select data from a page in the table that you've got locked, then yes, you'll block 'em. You can work around that with things like the NOLOCK hint on a select statement or by using Read Committed Snapshot Isolation. For a starting point on how isolation levels work, check out Kendra Little's isolation levels poster.

How long will it take before it causes a deadlock? Will that time depend on how much load is on the server, e.g. if there is not much load will it take longer to cause a deadlock?

Deadlocks aren't based on time - they're based on dependencies. Say we've got this situation:

• Query A is holding a bunch of locks, and to finish his query, he needs stuff that's locked by Query B
• Query B is also holding a bunch of locks, and to finish his query, he needs stuff that's locked by Query A

Neither query can move forward (think Mexican standoff) so SQL Server calls it a draw, shoots somebody's query in the back, releases his locks, and lets the other query keep going. SQL Server picks the victim based on which one will be less expensive to roll back. If you want to get fancy, you can use SET DEADLOCK_PRIORITY LOW on particular queries to paint targets on their back, and SQL Server will shoot them first.

Is there a way to monitor and see what is locked at any particular time?

Absolutely - there's Dynamic Management Views (DMVs) you can query like sys.dm_tran_locks, but the easiest way is to use Adam Machanic's free sp_WhoIsActive stored proc. It's a really slick replacement for sp_who that you can call like this:

sp_WhoIsActive @get_locks = 1

For each running query, you'll get a little XML that describes all of the locks it holds. There's also a Blocking column, so you can see who's blocking who. To interpret the locks being held, you'll want to check the Books Online descriptions of lock types.

If each thread is doing queries on single tables, is there then a case where blocking can occur? So isn't it the case that a deadlock can only occur if you have a query which has a join and is acting on multiple tables?

Believe it or not, a single query can actually deadlock itself, and yes, queries can deadlock on just one table. To learn even more about deadlocks, check out The Difficulty with Deadlocks by Jeremiah Peschka.

If you have direct control over the SQL, you can force row level locking using:

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

These two answers might be helpful:

https://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server

https://stackoverflow.com/questions/1625784/locking-a-table-with-a-select-in-entity-framework

To view current held locks in Management Studio, look under the server, then under Management/Activity Monitor. It has a section for locks by object, so you should be able to see whether the inserts are really causing a problem.

Deadlock errors generally return quite quickly. Deadlock states do not occur as a result of a timeout error occurring while waiting for a lock. Deadlock is detected by SQL Server by looking for cycles in the lock requests.

The best answer I can come up with is: It depends.

The best way to check is to find your connection SPID and use sp_lock SPID to check if the lock mode is X on the TAB type. You can also verify the table name with SELECT OBJECT_NAME(objid). I also like to use the below query to check for locking.

    SELECT RESOURCE_TYPE,RESOURCE_SUBTYPE,DB_NAME(RESOURCE_DATABASE_ID) AS 'DATABASE',resource_database_id DBID,
    RESOURCE_DESCRIPTION,RESOURCE_ASSOCIATED_ENTITY_ID,REQUEST_MODE,REQUEST_SESSION_ID,
    CASE WHEN RESOURCE_TYPE = 'OBJECT' THEN OBJECT_NAME(RESOURCE_ASSOCIATED_ENTITY_ID,RESOURCE_DATABASE_ID) ELSE '' END OBJETO
    FROM SYS.DM_TRAN_LOCKS (NOLOCK)
    WHERE REQUEST_SESSION_ID = --SPID here

In SQL Server 2008 (and later) you can disable the lock escalation on the table and enforce a WITH (ROWLOCK) in your insert clause effectively forcing a rowlock. This can't be done prior to SQL Server 2008 (you can write WITH ROWLOCK, but SQL Server can choose to ignore it).

I'm speaking generals here, and I don't have much experience with BLOBs as I usually advise developers to avoid them, especially if larger than 1 MB.