transactions - tempdb SQL Server locking -

Our application runs on a client machine with another application. We have made some attempts to avoid long-running locks in tempdb because this clearly affects the concurrency. Other applications, however, works like this:

Start the transaction # to create # tables (...); Insert into # tables (....) values ​​(...);
operation_for_totally_synx_second ()
committed;

As operations take time, our application is waiting for the lock received by other apps.

Now, I hope that there may be a way to separate my application from other applications, for example I have been asked to provide other tempi to SQL Server, but I have no way have been found. Is it any way possible or is there a solution to install our database on any other mssql-instance?

Regards, Jens Nordenbro

Long-lasting locks Tempdb This clearly affects solidarity

This is not really clear at all. Long-held locks are of importance only if you and other applications go after the same locale. The code sample you posted is completely valid. Firstly #temp is a connection specific table that no other connection can see. But even if this is a global resource, it will be related to other applications and therefore you will not have any business on it.

As an exercise, open an SSMS query window and run it:

  Start the transaction; Create table #temp (an integer);  

Then open another query window and run one. QED, they do not block each other, despite creating very #temp tables.

If tempdb is actually a bottle neck, then you need to check some more and need to find actual resources that are on dispute. / P>