mysql DBI 事务控制

时间：2024-01-21 18:43:33
事务；

事务是任何健壮数据库系统的基本组成,它们 防止错误和数据库腐败通过确保有关数据的改变是原子发生的(不可分割的,要么所有要么什么都不做)

这个章节应用于数据库支持事务和 AutoCommit 是关闭的,查看"AutoCommit" 使用对于各种类型的数据库。

推荐的方式实现健壮的事务在Perl应用是使用RaiseError and eval { ... } 

  $dbh->{AutoCommit} = 0;  # enable transactions, if possible

  $dbh->{RaiseError} = 1;

  eval {

      foo(...)        # do lots of work here

      bar(...)        # including inserts

      baz(...)        # and updates

      $dbh->commit;   # commit the changes if we get this far

  };

  if ($@) {

      warn "Transaction aborted because $@";

      # now rollback to undo the incomplete changes

      # but do it in an eval{} as it may also fail

      eval { $dbh->rollback };

      # add other application on-error-clean-up code here

  }

  #!/usr/bin/perl

use DBI;

$db_name='zjzc';

$ip='127.0.0.1';

$user="root";

$passwd="1234567";

$dbh="";

$dbh = DBI->connect("dbi:mysql:database=$db_name;host=$ip;port=3306",$user,$passwd,{

                          RaiseError => 1,

                          AutoCommit => 0

                        }) or die "can't connect to database ". DBI-errstr;

eval{

$dbh->do("insert into test values('$ARGV[0]')") ;

sleep (100);

$dbh->commit();};

if( $@ ) {

    #warn "Database error: $DBI::errstr\n";

    		 $dbh->rollback(); #just die if rollback is failing

   		 	 };

    		 	 	 $dbh->disconnect; 

如果RaiseError 熟悉没有被设置,DBI 请求需要手动检查错误,像这样:

$h->method(@args) or die $h->errstr;

RaiseError设置后,DBI会自动的die 如果DBI方法调用处理失败,你不需要测试每个方法的返回值

评价方法的主要有点是 事务会正确的回滚 当任何代码(不只是DBI请求) 在内部程序dies 由于任何原因。

使用 $h->{RaiseError} 属性的主要优势是 DBI请求会被自动检查,

[root@wx03 sbin]# cat a1.pl

use DBI;

use POSIX;

$db_name='scan';

$ip='127.0.0.1';

$user="root";

$passwd="xxx";

$dbh = DBI->connect("dbi:mysql:database=$db_name;host=$ip;port=3306",$user,$passwd,{

                          RaiseError => 1,

                          AutoCommit => 0

                        }) or die "can't connect to database ". DBI-errstr;

eval{

my $XDATE = strftime("%Y%m%d%H%M%S",localtime());

print "\$XDATE is $XDATE\n";

$dbh->do("update t1 set c1=999 where c1=100") ;

$dbh->commit();};

if( $@ ) {

my $XDATE = strftime("%Y%m%d%H%M%S",localtime());

    #warn "Database error: $DBI::errstr\n";

print "\$XDATE is $XDATE\n";

    		 $dbh->rollback(); #just die if rollback is failing

   		 	 };

    		$dbh->disconnect;

[root@wx03 sbin]# perl a1.pl

$XDATE is 20160823110539

DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at a1.pl line 14.

$XDATE is 20160823110630

| innodb_lock_wait_timeout                | 50

[root@wx03 sbin]# perl a1.pl

$XDATE is 20160823110743

DBD::mysql::db do failed: Table 'scan.t19' doesn't exist at a1.pl line 14.

$XDATE is 20160823110743

[root@wx03 sbin]# cat a1.pl

use DBI;

use POSIX;

$db_name='scan';

$ip='127.0.0.1';

$user="root";

$passwd="xxx";

$dbh = DBI->connect("dbi:mysql:database=$db_name;host=$ip;port=3306",$user,$passwd,{

                          RaiseError => 1,

                          AutoCommit => 0

                        }) or die "can't connect to database ". DBI-errstr;

eval{

my $XDATE = strftime("%Y%m%d%H%M%S",localtime());

print "\$XDATE is $XDATE\n";

$dbh->do("update t19 set c1=999 where c1=100") ;

$dbh->commit();};

if( $@ ) {

my $XDATE = strftime("%Y%m%d%H%M%S",localtime());

    #warn "Database error: $DBI::errstr\n";

print "\$XDATE is $XDATE\n";

    		 $dbh->rollback(); #just die if rollback is failing

   		 	 };

    		$dbh->disconnect;

[root@wx03 sbin]# perl a1.pl

$XDATE is 20160823110746

DBD::mysql::db do failed: Ta

相关文章