如果插入了一条记录，并且在同一事务中删除了同一条记录，那么这是否算作插入? [英] If a record is inserted and if the same record is deleted in the same transaction, does that count as an insertion at all?

问题描述

如果我有一个数据库事务，其中在同一事务中创建和删除了一个数据库，那么该数据库事务是否算作插入操作?

谢谢！

解决方案

假设您要插入一行，然后在您的问题中将其删除.

从发生的角度来看，它是发生了并且在INNODB中被颠倒了，是的.

请参阅以下内容:

create table foo
(   id int auto_increment primary key,
    thing varchar(20) not null
)ENGINE=INNODB;

start transaction;
insert foo(thing) values ('frog');
commit;

start transaction;
insert foo(thing) values ('frog2');
-- Point-in-time A
rollback;

start transaction;
insert foo(thing) values ('frog3');
commit;

select * from foo;
+----+-------+
| id | thing |
+----+-------+
|  1 | frog  |
|  3 | frog3 |
+----+-------+

删除或rollback显示innodb间隙异常，这表示保留了范围然后发生了删除.换句话说，保留了AUTO_INCREMENT，将其插入插槽，然后回滚(或删除).

不仅如此，还取决于上面的时间点A"，具体取决于此处的缩写答案. 很多易于创建间隙的案例，例如插入期间的外键错误.

If I have a database transaction where a database is created and deleted within the same transaction, would it be counted as an insertion?

Thanks!

解决方案

Assuming you meant a row is inserted then deleted in your question.

From the standpoint of did it happen and get reversed in INNODB, yes.

See the below:

create table foo
(   id int auto_increment primary key,
    thing varchar(20) not null
)ENGINE=INNODB;

start transaction;
insert foo(thing) values ('frog');
commit;

start transaction;
insert foo(thing) values ('frog2');
-- Point-in-time A
rollback;

start transaction;
insert foo(thing) values ('frog3');
commit;

select * from foo;
+----+-------+
| id | thing |
+----+-------+
|  1 | frog  |
|  3 | frog3 |
+----+-------+

The deletion or the rollback shows the innodb gap-anomaly which means the range was reserved then the removal happened. In other words, the AUTO_INCREMENT was reserved, was slotted, then rolled-back (or deleted).

Not only that, but at "Point-in-time A" above, depending on the Transaction Isolation Levels set at the time, other users in the system could even see your frog2 row above. That was rolled back in the next call.

See an abbreviated answer of mine Here on various innodb gap-anomoly issues. There are many easy to create cases for the gaps such as Foreign Key errors during inserts.

这篇关于如果插入了一条记录，并且在同一事务中删除了同一条记录，那么这是否算作插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！