I've just written a small test app that generates 10 threads, each of which instantiates an adapter instance. Each thread attempts 1000 add / update operations, inside a transaction, on the same table (add a new entity, add another entity, use UpdateEntitiesDirectly with a RelationPredicateBucket to update the previously added entity. I'm getting loads of exceptions thrown:

Transaction was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction...

The SQL is always the same:

UPDATE <TABLE> WHERE <TABLE>.parentId = @parentId1

I was under the impression that the adapter could be used from multiple threads as long as each thread has its own instance of the adapter. Or is it just the database (SQLServer) throwing a wobbly at having so many transactions started in a short space of time?

Also, performing 1000 of these operations from a single thread is orders of magnitude faster than performing 500 operations in 2 separate threads. Looks like some under-the-hood contention slowing things down in a multi-threaded environment... Is this a limitation of LLBLGen, or should paying more attention to the database access used speed things up?

For 1st question: I think this is caused by dead locks inside your database.

For the 2nd question: I think that's because of the overhead of creating a database connection. Also it depends on whether you are running 2 different threads on 2 different processors / machines or using just one processor. I think threading in one processor won't sepead things up, unless you have some I/O in one thread and you don't want to slow down other actions so you put them in another thread.

1st point - have to re-look at the DB design then... Arse.

2nd point - It's just a one processor, single core machine. I don't get your explaination of database connection creation overhead. The code is identical in each case:

for(int i = 0; i < 1000; i++) { using(IDataAccessAdapter adapter = new DataAccessAdapter()) { TMyEntity = new TMyEntity(); // Do stuff adapter.Save(TMyEntity); // Etc etc } }

It's just in the first case I run this once, in the second case the loop constraint is i < 500 and I'm running two concurrent threads. I can't see how the slowdown in performance (which is huge, I can time it if you'd like) is relevant to creating database connections when I'm making the same number in each test. I also can't imagine that repeatedly switching the thread context (due to this running on a single processor) would result in this slowdown.

1st question:

I think as you are issuing several row locks, SQL server decides to escalate from row locks to page or table locks and this is causing the dead lock in the SQL server, because the process (there is one process for each connection) are trying to get lock of the same page or table. It will be interesting if you do the same test but each thread updating difference table.

The second question depend of: - The overhead of creating a second thread and connection. - The fact that SQL server is scaling from row locks to page or table locks, SQL server does this for performance. Think the following: you are changing 1000 rows and after the first 50 SQL server decides to escalate from row locks to table locks, then the other 950 rows do not need to be locked and this is a gain in performance.

I also think it's the lock promotion that makes it slow. So in short I second Rogelio's and Walaa's excellent explanations