Begin transaction is blocking my entire database

Question

Begin transaction is blocking my entire database

Navigation

#1 by (1 votes)

2

I have a problem, I have a script that makes reading large files in XML.

Before starting the insertion of these data, I do a begin transaction and end the commit , however throughout the process it may take up to 20 minutes my database unusable, so much for who is doing the import (so far ok) so much for other computers that has nothing to do with the table that is being populated, can you give me a light?

php sql-server

asked by anonymous 03.02.2015 / 20:10

1 answer

Avoid ListView overlay "undefined is not a function" when calling "tabs"

score 1 · Answer 1

Bruno,

When we open a transaction all the tables involved in your script will be locked for use until the transaction is completed. SQL Server, and other DBMSs, use this technique to prevent others from reading a phantom, dirty, or outdated data that is unreliable data.

I found a more detailed explanation of this case Isolation Level . In this link, you can find two solutions for this:

Set the Isolation Level to READ UNCOMMITTED in out-of-transaction queries;

Use the WITH (NOLOCK);

Here's an example of their uses:

Isolation Level:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED         
    SELECT * FROM tbIsolationLevel

WITH (NOLOCK):

SELECT * FROM tbIsolationLevel WITH (NOLOCK)

Remembering that you will have problems reading untrusted data in both solutions.