我想我很困惑选择更新 https://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html构造。
例子:
mysql> select * from employees2;
+-------+----------+--------+-----------+
| EmpId | EmpName | DeptId | EmpSalary |
+-------+----------+--------+-----------+
| 1 | John | 1 | 5000.00 |
| 2 | Albert | 1 | 4500.00 |
| 3 | Crain | 2 | 6000.00 |
| 4 | Micheal | 2 | 5000.00 |
| 5 | David | NULL | 34.00 |
| 6 | Kelly | NULL | 457.00 |
| 7 | Rudy | 1 | 879.00 |
| 8 | Smith | 2 | 7878.00 |
| 9 | Karsen | 5 | 878.00 |
| 10 | Stringer | 5 | 345.00 |
| 11 | Cheryl | NULL | NULL |
+-------+----------+--------+-----------+
11 rows in set (0.00 sec)
我在脚本中执行以下操作:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('dbi:mysql:testdb','root','1234', {'RaiseError' => 1, 'AutoCommit' => 0}) or die "Connection Error: $DBI::errstr\n";
my $sql = "select * from employees2 where EmpId IN (2,10) for update";
my $sth = $dbh->prepare($sql);
$sth->execute or die "SQL Error: $DBI::errstr\n";
while (my @row = $sth->fetchrow_array) {
print "@row\n";
}
sleep(9000);
$dbh->commit;
我还并行一个控制台并连接到数据库。
所以我运行脚本first然后在另一次会议中我这样做:
mysql> select * from employees2 where EmpId IN (10) for update;
第二个选择块,因为它引用同一行。
这会阻止我这样做:
mysql> set autocommit = 0;
mysql> begin;
mysql> select * from employees2 where EmpId IN (10) for update;
mysql> commit;
要不就
mysql> select * from employees2 where EmpId IN (10) for update;
因此,无论是否在事务中,它都会阻塞无关紧要的事情。
现在,如果我将脚本更改为:
my $dbh = DBI->connect('dbi:mysql:practice','root','') or die "Connection Error: $DBI::errstr\n";
即脚本确实not在一个事务内运行第二个会话不会阻塞!
为什么会阻塞only脚本是否在事务中运行?