Databases
StackOverflow2013
Postsdbo
POLinq to SQL Transaction Insert then Select really, really slow
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq to SQL Transaction Insert then Select really, really slow
    text
    copied!<p>I'm developing a piece of a system that basically migrates data from one set of tables to another set. Everything works fine, but I've decided to employ transactions instead of just failing on things that are partially completed. (That is, if some exception occurs, I want to rollback instead of having partial data migrated.)</p> <p>I have a service (in the 3-tier architecture way, not web) which begins a transaction on the data access layer. The data context is shared in the data access class which contains many methods. Those methods use various LINQ-to-SQL techniques to update/insert/delete. All the LINQ-to-SQL "selects" are within CompiledQueries. </p> <p>The "BeginTransaction" method starts a transaction like this:</p> <pre><code>Public Sub BeginTransaction() Implements ITransactionalQueriesBase.BeginTransaction Me.Context.Connection.Open() Me.Context.Transaction = Context.Connection.BeginTransaction() IsInTransaction = True End Sub </code></pre> <p>Basically, I have written a test which starts a transaction, inserts into a table, and then attempts to retrieve the value that was just inserted, all during the transaction. I did this because I wanted to assert that the insert method actually tries to insert. Then, during the test I would rollback, then test to ensure that the newly inserted value is not actually committed to the table. The test looks something like this:</p> <pre><code>&lt;TestMethod()&gt; Public Sub FacilityService_Can_Rollback_A_Transaction() faciService.BeginTransaction() Dim devApp = UnitTestHelper.CreateDevelopmentApplication(devService.GetDevelopmentType("NEWFACI").ID, 1, 1, 1, 1) Dim devInsertRes = devService.InsertDevelopmentApplication(devApp) Assert.IsTrue(devInsertRes.ReturnValue &gt; 0) For Each dir1 In devInsertRes.Messages Assert.Fail(dir1) Next Dim migrationResult = faciService.ProcessNewFacilityDevelopment(devInsertRes.ReturnValue) Assert.IsTrue(migrationResult.ReturnValue.InsertResult) Dim faciRetrieval1 = faciService.GetFacilityByID(migrationResult.ReturnValue.FacilityID) Assert.IsNotNull(faciRetrieval1.ReturnValue) faciService.Rollback() Dim faciRetrieval2 = faciService.GetFacilityByID(migrationResult.ReturnValue.FacilityID) Assert.IsNull(faciRetrieval2.ReturnValue) End Sub </code></pre> <p>So, to my problem...</p> <p>When the test gets to the "faciRetrieval1" step, it stays there for about 30-60 seconds before moving on. I'm not sure why this is happening. If I run the same queries in a transaction within SSMS it happens instantly. Does anyone have any ideas? The database is a SQL Server 2008 SP1 (R2?).</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload