两个进程同时在数据库中保存记录 [英] Two processes saving a record in database at the same time

问题描述

当多个用户在同一时间在数据库（MySQL，Postgres）中插入数据时会发生什么？如何优先考虑先插入哪个记录，然后再插入哪个记录。如果答案是特定于程序的应用程序，我要求参考网络应用程序。

What happens when more than one user inserts data in Database (MySQL, Postgres) at exactly same time? How does it prioritize which record to be inserted first and which one later. If the answer is specific to application of program, I am asking in reference to web-applications.

推荐答案

它是不完全一样时间。一个会发生在另一个之前。
除非你实现自己的优先级机制，否则将是不确定的，你不应该依赖它。

It's never exactly the same time. One will happen before the other. Which one will, unless you implement your own prioritisation mechanism, is indeterminate, and you should never rely on it.

至于会发生什么，

对于同一个表的两个插入，如果数据完整性取决于它们在数据库设计中执行的顺序是否具有可怕的缺陷。

For two inserts to the same table, if data integrity is dependant on what order they are executed in your database design has a horrendous flaw.

对于碰撞（例如对同一记录的两次更新）。有两种实现方式。

For collisions (two updates to the same record for instance). There are two implementations.

悲观锁定。假设有相当数量的更新对相同的数据，所以发出锁定。如果锁存在失败，更新（例如第二个如果第一个尚未完成）和一些合适的消息。

Pessimistic locking. Assume there will be a significant number of updates to teh same data, so issue a lock around it. If The lock exists fail the update (e.g. second one if first hasn't finished) with some suitable message.

乐观锁。假设碰撞很少发生。通常的做法是向记录中添加一个时间戳字段，该记录将更新每个更新。所以当你读取数据时，你得到的时间戳，当你写数据时，你只做它，如果你的时间戳匹配现在的那个，并更新时间戳作为它的一部分。如果不匹配，请执行其他人已更改此数据消息。

Optimistic locking. Assume collisions will rarely happen. Usual way of doing this is to add a timestamp field to the record which changes every update. So when you read the data you get the timestamp, and when you write the data you only do it, if the timestamp you have matches the one that's there now, and update said timestamp as part of it. If it does not match you do the "Someone else has changed this data message".

有一个妥协位置，您可以尝试并合并两个更新。 （例如您更改名称和我更改地址）。你需要真正想到这一点，它是混乱，很快就变得非常复杂，并得到它错误运行的一个真正的风险，搞乱数据。

There is a compromise position, where you try and merge two updates. (for instance you change name and I change address). You need to really think about that though, it's messy, and get very complicated very quickly, and getting it wrong run's a real risk of messing up the data.

远远大于智商比我花了很多时间在这个东西，个人我喜欢保持它像我一样简单...

People with far larger IQs than mine spend a lot of time on this stuff, personally I like to keep it like me, simple...

这篇关于两个进程同时在数据库中保存记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！