in reply to Dead Lock replication in Sql server

I have some questions since your question is vague and we cannot give you a good answer without understanding the question.

Do you have code that reproduces the database deadlock?

Do you get a database deadlock while replicating the database to another server?

Is your problem that you need to replicate a specific database deadlock situation?

Do you have problems using Exception::Class::TryCatch?

If you have problems using Exception::Class::TryCatch, please show us the code you have already written and tell us how it fails for you.

  • Comment on Re: Dead Lock replication in Sql server

Replies are listed 'Best First'.
Re^2: Dead Lock replication in Sql server
by sunil19 (Initiate) on Jun 30, 2017 at 11:09 UTC
    Hello Corion, I need a simple code for replicating deadlock in sql server.After replicating the deadlock issue i need code to fix the deadlock issue by using the Exception::Class::TryCatch. Thanks
[reply]

      If you need code to replicate a deadlock in Microsoft SQL Server, I think you are wrong here. Creating a deadlock in a database is not a Perl problem, it is more an SQL problem.

      Once you have the SQL to create the deadlock, use DBI to send the SQL to the server.

      Then fix the issue by using Exception::Class::TryCatch.

[reply]

        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).

[reply]
[d/l]
[select]
        i have code that can replicate the dead lock issue.can you help me to code to using DBI to send tht SQL to server and also the fix it by using Exception::Class::TryCatch.
[reply]
[reply]

In Section Seekers of Perl Wisdom