in reply to Re^3: Dead Lock replication in Sql server
in thread Dead Lock replication in Sql server

Here is my sample code which iam using to replicate the dead lock and using trycatch to handle the dead lock

Transaction A:sample 

use strict;
use DBI;
use Execption::Class:TryCatch;

###opening connections

try eval{
   my $sql = "BEGIN TRAN \n";
   my $sql1 = "Update dead_test set a = '11' where b= 1 \n";
   my $sql2  = "WAITFOR DELAY '00:00:05'\n"
   my $sql3 = "Update dead_test1 set a = '22' where b= 2 \n";
   my $sql4 = "COMMIT \n" ; 
    $sql = $sql.$sql1.$sql2.$sql3.$sql4;
      $sql  = $dbh->pepare($sql);
    $sql ->execute();

};
if catch(my $err){

  print  $err;

eval {
   $dbh - >rollback };
}

[download]

Transaction B:sample 

use strict;
use DBI;
use Execption::Class:TryCatch;

###opening connections

try eval{
   my $sql = "BEGIN TRAN \n";
   my $sql1 = "Update dead_test1 set a = '11' where b= 2 \n";
   my $sql2  = "WAITFOR DELAY '00:00:05'\n"
   my $sql3 = "Update dead_test set a = '22' where b= 1 \n";
   my $sql4 = "COMMIT \n" ; 
    $sql = $sql.$sql1.$sql2.$sql3.$sql4;
      $sql  = $dbh->pepare($sql);
    $sql ->execute();

};
if catch(my $err){

  print  $err;

eval {
   $dbh - >rollback };
}

[download]

Iam executing both the transaction from 2 unix sessions parallel. As of now it doesn;t return or print any error. The question is how to capture the error from that is return from database and rollback if it is a deadlock error (erronumber 1205).

Replies are listed 'Best First'.
Re^5: Dead Lock replication in Sql server
by Eily (Monsignor) on Jun 30, 2017 at 12:29 UTC

    FYI, you can create a multiline string with this syntax: 

    my $sql = <<_SQL_;
BEGIN TRAN 
Update my_test1 set a = '33' where b= 2 
WAITFOR DELAY '00:00:05'
Update my_test set a = '44' where b= 1 
COMMIT 
_SQL_

    [download]

    Your code has many typos and mistakes, there's no way it even compiles. So you can't have been executing that.

[reply]
[d/l]

      I haven't copied my code exactly the way it is. its just and mock up example. I trying to use TryCatch package to catch my exception that returns from the database (sql server). But i couldn't able to do it ( my experience in using perl is 4 days). If there are any example which i can refer where the database throws certain error (say Deadlock 1205) i need to handle data error.please let me know if there are any references or links which can i refer. Thanks

[reply]

        Try

        #!/usr/bin/perl
use warnings;
use strict;
use DBI;
use Exception::Class::TryCatch; 

my $dbh = dbh(); # get a handle 

my $sql =<< 'SQL';
  BEGIN TRANSACTION 
    UPDATE dead_test  SET a = '11' where b=1
    WAITFOR DELAY '00:00:05'
    UPDATE dead_test1 SET a = '22' where b=2
  COMMIT
SQL
my $sth = $dbh->prepare($sql);
 
eval { $sth->execute() }; 
if ( catch my $err){ 
  if ($err =~ /deadlocked/){
    print $err->error;
    eval { $dbh->rollback };
  }  
}

# connect
sub dbh {
  my $dsn = "DBI:ODBC:mssql";
  my $dbh = DBI->connect($dsn, 'user', 'password',
     { RaiseError => 1, 
       PrintError => 0,
       AutoCommit => 0 }) 
  or die (Error connecting " $DBI::errstr");
}

        [download]
        poj
[reply]
[d/l]
Re^5: Dead Lock replication in Sql server
by AnomalousMonk (Archbishop) on Jun 30, 2017 at 15:47 UTC
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom