SQL server transaction issue using perl DBI

I have the below code block with a INSERT and an UPDATE statement inside a BEGIN TRAN and END TRAN block.

Now we all know that this operation is atomic. But i am seeing otherwise. the insert fails due to a unique constraint violation , but still the UPDATE happens. When I ex开发者_如何学Pythonecute the same piece in SQL server directly , i can see that the operation is perfectly atomic. Is there anything on the PERL DBI front which is overriding the BEGIN TRAN and COMMIT TRAN. Any Pointers ?

$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 1;

my $sql = "BEGIN  TRAN

         INSERT INTO $table ...

         UPDATE $table ....

         COMMIT TRAN";
$dbh->do($sql);

---

I'm assuming you are using DBD::ODBC so bare this in mind when you read the following.

By default DBI will be in AutoCommit mode unless you disabled it.

Don't pass multiple pieces of SQL which uses transactions like this to SQL Server via the do method as DBD::ODBC will use SQLExecDirect for do by default and do is not really designed with multiple statements in mind. You are better using prepare/execute or splitting your SQL up and doing the commit in Perl like this:

$dbh->{RaiseError} = 1;
$dbh->begin_work;
eval {
  $dbh->do(q/insert.../);
  $dbh->do(q/update.../);
  $dbh->commit;
  1;
};
if ($@) {
  $dbh->rollback or warn "rollback failed";
}

or putting your insert/update in a SQL Server procedure.