事务中的 LAST_INSERT_ID() 是否可靠? [英] Is LAST_INSERT_ID() in a transaction dependable?

问题描述

我正在使用 mysql/ado.net/C# 这是我的问题我知道 mysql 是并发的，但是我有文件数据(缩略图名称)和 db 数据(行)要同步.

I am using mysql/ado.net/C# heres my problem I know mysql is concurrent, however i have file data (the thumbname name) and db data (the row) to be in sync.

如果我开始交易但由于任何原因失败，这会是一个问题吗?如果我让这段代码在两个核心上同时运行，它们会互相破坏吗?我基本上需要知道 1) last_insert_id 是否可靠而不改变.2) 如果一个事务使用所述rowid，则另一个事务不会使用它.

If i start a transaction and it fails for any reason will this be a problem? If i have this code ran at the same time on two cores will they clobber eachother? I essentially need to know if 1) the last_insert_id is dependable on not changing. 2) if one transaction uses said rowid that another transaction wont use it.

start transaction
insert statement, however i dont want it to be active yet;
select LAST_INSERT_ID()
File.Delete(lastid)//may exist from a transaction that failed
File.Move(thumbImage, lastid)
transaction.commit()//ok done

这样安全吗?为什么或为什么不?

Is this safe? Why or why not?

推荐答案

MySQL 的 last_insert_id() 是可靠的，因为它始终是特定连接执行的最后插入.它不会报告由其他连接创建的插入 ID，也不会报告您在两个连接之前执行的插入.实际插入发生在哪个 cpu 核心上以及处理 last_insert_id() 调用的核心都无关紧要.它始终是该连接的正确 ID 号.

MySQL's last_insert_id() is dependable in that it's always the LAST insert performed by THAT PARTICULAR connection. It won't report an insert id created by some other connection, it won't report an insert that you did two connections ago. It won't matter which cpu core the actual insert occured on, and which core the last_insert_id() call is processed on. It will always be the right ID number for that connection.

如果您回滚执行插入的事务，last_insert_id() 仍会报告该新 id，即使它不再存在.但是，在随后的插入中，该 ID 不会被重用

If you roll back a transaction that did an insert, last_insert_id() will STILL report that new id, even though it no longer exists. The id will not be reused, however, in a subsequent insert

这篇关于事务中的 LAST_INSERT_ID() 是否可靠?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！