Begin transaction is blocking my entire database

Asked

Viewed 505 times

2

I got a problem, I got a problem script php that reads large XML files.

Before I start inserting this data I make a Begin transaction and in the end the commit, but throughout the process that can take up to 20 minutes my database is unusable, both for those who are importing (so far ok) and for other computers that has nothing to do with the table that is being populated, you can give me a light?

php sql-server transactions

  • Which database is it? you give some lock manually?

  • I am using sql server 2008. I do not use any lock, all settings are default installation on sql server

  • 1

    Put the code on for us to check it out!

  • Could put part of php and sql code.

  • Hm, I’m using the Adodb class for PHP with the mssqlnative driver. The call it executes is for the "sqlsrv_begin_transaction" function and after that I do the Insert normally and then the commit. In the middle of this transaction I’m running some selects, you think the tables I run select inside the trans are being blocked ?

  • 1

    When you call sqlsrv_begin_transaction you do not pass any parameter? What if for each select you select within this transaction you do within a new transaction? Have you tried this?

Show 1 more comment

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 Dbms, use this technique to prevent others from reading a ghost data, dirty or outdated which is an unreliable data.

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

  1. Set Isolation Level to READ UNCOMMITTED in out-of-transaction queries;
  2. Use WITH (NOLOCK);

An example of its 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 non-conconflated data in both solutions.

Browser other questions tagged php sql-server transactions

You are not signed in. Login or sign up in order to post.