事务不适用于我的 MySQL 数据库 [英] Transactions not working for my MySQL DB

问题描述

我的站点使用 MySQL 数据库，该数据库托管在 Linux 共享服务器上.

I'm using a MySQL DB for my site, which is hosted on a Linux shared server.

我编写了一个测试脚本，我使用mysql"运行该脚本来测试事务是否正常工作.运行脚本，我没有得到任何错误，但执行脚本的结果就像没有启用事务.

I wrote a test script which I run using 'mysql' to test if transactions are working ok. Running the script, I do not get any error, but the result of executing the scripts is as if transaction is not enabled.

我还确保将所有权限授予运行脚本的管理员 MySQL 用户.

I also made sure to grant ALL privileges to the admin MySQL user which runs the script.

为了再次检查，我在 PostgreSQL 上尝试了相同的测试脚本，然后 - 脚本的结果表明事务确实有效.所以它绝对是特定于 MySQL 的东西.

In order to double check, I tried the same test script on PostgreSQL, and there - the result of the script indicated that transaction does work. So it's definitely something which is specific to MySQL.

脚本在我创建的一个简单表上运行，如下所示:

The script runs on a simple table which I created as follows:

create table a ( id serial primary key);

以下是测试脚本:

delete from a;
set autocommit = 0;
start transaction;
insert into a(id) values(1);
rollback work;
select count(*) from a;

所以脚本确保表是空的，然后它开始一个事务，插入一行并回滚插入.当插入"被回滚时，选择"应该表明表包含 0 行.

So the script makes sure the table is empty, Then it starts a transaction, insert a row and rollback the insert. As the "insert" is rolled back, the "select" should indicate that table contains 0 rows.

在 PostgreSQL 上运行:

Running this on PostgreSQL:

$ psql db admin < test1
DELETE 0
START TRANSACTION
INSERT 0 1
ROLLBACK
count
-------
     0

这是预期的行为，插入回滚时表中有 0 行.

This is the expected behavior, 0 rows in the table as the insert was rolled back.

在我的 MySQL 数据库上运行相同的:

Running the same on my MySQL DB:

$ mysql db -u admin < test1
count(*)
1

回滚后有 1 行表示插入"未回滚，就像在非事务模式下一样.

Having 1 row following the rollback indicate that the "insert" was not rolled back, just as in non-transaction mode.

如前所述，管理员被授予对数据库的所有权限.

As mentioned, admin is granted with ALL privileges to the DB.

有什么我遗漏的吗?

推荐答案

可能该表是默认使用 MyISAM 存储引擎创建的.MyISAM 存储引擎不支持事务.

Probably the table is created with the MyISAM storage engine as default. MyISAM storage engine doesnt support transactions.

创建表格

CREATE TABLE a ( id SERIAL PRIMARY KEY) ENGINE = MYISAM;

查询

DELETE FROM a;
SET autocommit = 0;
START TRANSACTION;
INSERT INTO a(id) VALUES(1);
ROLLBACK WORK;
SELECT COUNT(*) FROM a;

结果

     1

制作表 InnoDB

查询

ALTER TABLE a ENGINE=INNODB; 

查询

DELETE FROM a;
SET autocommit = 0;
START TRANSACTION;
INSERT INTO a(id) VALUES(1);
ROLLBACK WORK;
SELECT COUNT(*) FROM a;

结果

count(*)  
----------
         0

这篇关于事务不适用于我的 MySQL 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！