Re^5: Dead Lock replication in Sql server

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.

Comment on Re^5: Dead Lock replication in Sql server Download Code

Replies are listed 'Best First'.
Re^6: Dead Lock replication in Sql server by sunil19 (Initiate) on Jul 03, 2017 at 05:56 UTC
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]
Re^7: Dead Lock replication in Sql server by poj (Abbot) on Jul 03, 2017 at 07:50 UTC
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]