事务不适用于我的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

Making the table 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屋！