When is a database table locked?

Question

Is a table locked every time I insert sth. in it? Because I want to insert thousands of rows each second so I want to know if having more than one thread for inserting helps effectively or not. If each insert locks the table just one thread is almost as efficient as for example 10 threads. If it depends on the Database engine, I use SQL Server 2008.

Thank you.

Answer 1

Given the database is the 'bottleneck' and not the work that the threads are doing, making all the inserts from different threads (using individual connection) will not help your performance. Especially if you do each insert in a new transaction. Every time a transaction is commit, the database needs to do work in order to ensure that data integrity is maintained. This is especially bad as all your transactions would be competing to do changes to the same object.

Instead of using multiple threads for your inserts you should make sure that you do everything in one single transaction and only commit when all the inserts are done.

BEGIN TRANSACTION;
INSERT INTO myTable (Col1, Col2) 'First' ,1;
INSERT INTO myTable (Col1, Col2) 'Second' ,2;
...
COMMIT;

N.B. - it is also possible to do all the inserts in one stament but that doesn't make a difference performance-wise:

INSERT INTO myTable (Col1, Col2)
    SELECT 'First' ,1
    UNION ALL
    SELECT 'Second' ,2
    UNION ALL
    SELECT 'Third' ,3;

In my opinion multiple inserts grouped in a transaction are much more straightforward. Plus, the second version might yield a nested statement that is too bit for the database to handle.

Answer 2

VoidPointer isn't necessarily correct. He isn't necessarily incorrect either. It depends why you have a multi-threaded application. If the threads are purely designed as a mechanism to 'insert more data' then no it probably won't improve performance. If however the threads serve some other purpose, then it suggests your latency is maybe elsewhere in the system and you shouldn't be worrying too much about the database. Why have you got all these threads? Presumably you're trying to make some other operation more efficient - like multiple I/O bound calls that complete at unknown intervals but reduce overall latency to the length of the longest running thread? Without a bit more context on what you're trying to achieve and why, you can't simply pronounce that a multi-threaded solution is wrong.

Have a look at some of the locking hint options SQL Server supports..

I think SQL Server supports concurrent INSERTs