Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Perl Query Locks A MsSQL Database

by EEddieEmm (Initiate)
on Jan 23, 2013 at 18:12 UTC ( #1014976=perlquestion: print w/replies, xml ) Need Help??
EEddieEmm has asked for the wisdom of the Perl Monks concerning the following question:

Dear Perl Monks

I am running a Perl script on a Linux server and accessing a MsSQL database on another server. Most everything is fine except for one query. When I run a SELECT, parse through the returned data and then attempt a table update on the same table, the database locks. When I go to the database itself and try to look at the table, I get the "Waiting to execute query" message. When I kill the script back on the Linux server, everything immediately starts to work again with no issues.

The script and query throw no errors. There is no indication that something is wrong. Has anyone else seen anything like this? If you have, what was your solution?

I have a code snippet below that shows what I am trying to do. The problem happens when I try to run the second query.


#!/usr/bin/perl -w use DBI; #### Make a database connection eval { $MY_CONNECTION = DBI->connect("$SERVER", "$UID", "$PWD", {PrintErr +or => 0}) or die "Cannot connect: $DBI::errstr\n"; }; if ($@) { print "ERROR: Database issue. The error was: $@"; exit(2); } #### Get the data my $GET_DB_DATA = $MY_CONNECTION->prepare("SELECT Field_1, Field_2, Fi +eld_3 " . "FROM " . $TableName . " " . + "WHERE Field_4 = 'foo' "); eval { $GET_DB_DATA->execute() or die "Database issue. $DBI::errstr\n"; }; if ($@) { print "ERROR: Cannot get table data. The error was: $@"; exit(2); } else { while ( my @RETURNED_DATA_ARRAY = $GET_DB_DATA->fetchrow ) { if ( $RETURNED_DATA_ARRAY[0] ne $RETURNED_DATA_ARRAY[1] ) { #### Update the table data my $UPDATE_TABLE = $MY_CONNECTION->prepare("UPDATE " . $Ta +bleName . " " . "SET Field_2 = +'" . $RETURNED_DATA_ARRAY[0] . "' " . "WHERE Field_3 += '" . $RETURNED_DATA_ARRAY[2] . "'"); eval { $UPDATE_TABLE->execute() or die "Database issue. $DBI: +:errstr\n"; }; if ($@) { print "ERROR: Cannot update table data. The error was +: $@"; exit(2); } } }

Replies are listed 'Best First'.
Re: Perl Query Locks A MsSQL Database
by moritz (Cardinal) on Jan 23, 2013 at 18:25 UTC

    Can you do the the same update in your databases' interface directly? Does it lock the table too?

    Also you can try to enable tracing in DBI (see section TRACING in DBI), maybe you'll find something interesting in the traces.

    Finally it might be a good idea to prepare the update query outside of the loop, and use placeholders.

      I can update the database directly. As a matter of of fact, that's how I "get around" the issue at the moment. I read the log and when an update is needed, I'll go into the database with Microsoft SQL Server Management Studio, and make the update with the data from the last log entry. Sucks, but it works.

      Being new to DBI, I didn't know how to do this. You are right, might be worth a look although I know where the problem is. Just don't know why.

      I was thinking along these lines myself. My plan was to alter the code to place the returned data into an array. Then read through that and make updates as needed. The while loop might be keeping the SELECT active somehow - don't know.

        Actually, yes, your $GET_DB_DATA statement handle will be active and maybe MySQLMSSQL prevents other queries from updating the table. I recommend reading all data before trying updates.

        Update: moritz spotted the wrong database

Re: Perl Query Locks A MsSQL Database
by wink (Scribe) on Jan 23, 2013 at 19:08 UTC

    Agree with Corion, try reading everything into a large array first then parse through it.

    Also, potentially a dumb question because I don't do a lot of DB work within Perl, but is there any reason you're executing your statement within an eval?

      "is there any reason you're executing your statement within an eval?"
      From perldoc -f eval : It is Perl's exception trapping mechanism; so you can trap errors and write eg :

      if ( $@ ) { if ( $@ =~ /foreign key/ ) { } else { } Logiciels de gestion des contentieux juridiques et des sinistres d'assurance
Re: Perl Query Locks A MsSQL Database
by RichardK (Parson) on Jan 23, 2013 at 19:29 UTC

    Why do it as two queries?

    Isn't it possible to do it with just one update?

    update table set field_2 = field_1 where field_4 = 'foo' and field1 != field_2;

    Or am I missing something?

Re: Perl Query Locks A MsSQL Database
by mje (Curate) on Jan 24, 2013 at 09:41 UTC

    I'm very surprised your code did not error with something like connection is busy with another statement. Until quite recently to get multiple active statements in MS SQL Server you had to do some rather nasty hacks (see Multiple Active Statements (MAS) and DBD::ODBC).

    I'd be interested in what DBD you were using and what library under that.

Re: Perl Query Locks A MsSQL Database
by tobyink (Abbot) on Jan 23, 2013 at 20:31 UTC

    Firstly, how are you connected? FreeTDS or ODBC? If you're connected through FreeTDS, it's pretty difficult to have two queries running concurrently on the same database handle. You need to finish your select before you run any updates. A workaround is to open two database handles on the same database.

    Secondly, by default MSSQL locks whole pages and often whole tables while one query is running, meaning a situation like yours is likely to cause deadlocks. You could experiment with adding some table hints to your select query; possibly ROWLOCK, perhaps even NOLOCK as a last resort.

    package Cow { use Moo; has name => (is => 'lazy', default => sub { 'Mooington' }) } say Cow->new->name
Re: Perl Query Locks A MsSQL Database
by nikosv (Chaplain) on Jan 23, 2013 at 20:24 UTC

    use SQL server's profiler to check for blocking locks.My guess is that the first cursor still has a shared lock on the rows read and while not yet completed you open another cursor (update) which conflicts with the previous one.

    What is your isolation level? try setting the isolation level for the session to READ UNCOMITTED just to check that it's a locking issue;remember this isolation level is not otherwise recommended

    you are using cursor-based procedural logic;try to mix your select with the update in one query and since I see that you check the values of 2 returned columns,use a CASE clause to place your filter and do your update

    or read all rows in an array,commit the select and then iterate through it in the client's memory,opening a cursor and doing your updates. However this has the drawback that your update might fail if another transaction has already changed your row in the mean time,since you take no shared/read locks

Re: Perl Query Locks A MsSQL Database
by EEddieEmm (Initiate) on Jan 23, 2013 at 21:04 UTC


    Thanks for all your input on this one. The issue was solved by reading all the data from the first SELECT query into a two dimensional array and then processing that.


    Does anyone know how to close out this issue?

      "Does anyone know how to close out this issue?"

      This is a discussion forum, not a bug tracker. We don't close threads when you've got the solution you were looking for. People may still want to post answers and observations in the future, and other people with the same problem who find this thread later on may prefer those solutions.

      package Cow { use Moo; has name => (is => 'lazy', default => sub { 'Mooington' }) } say Cow->new->name
      Some monks update the title of the original question by adding '[solved]' to it.
      لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1014976]
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2018-06-19 11:07 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (113 votes). Check out past polls.