避免在mysql中并发插入多个相同的id [英] avoiding concurrent insert multiple same id in mysql

问题描述

我的应用程序遇到问题.我有一张表，其中一个字段名称是 registration_no.在插入新记录之前，我将 registration_no 字段增加 1，然后在该表中插入增加的 registration_no .问题是当一些用户同时插入数据时，一些 registration_no 值是相同的.我怎样才能防止这种情况发生.

I am facing a problem in my application. I have a table that one field name is registration_no. Before inserting a new record i increment registration_no field by 1 and then insert that incremented registration_no in that table. the problem is when some user concurrently insert data some registration_no value has been same. how can i prevent this.

推荐答案

您想使用 序列.

两个警告:

• 文章中描述的 AUTO_INCREMENT 功能是非标准的，在移动到不同的数据库时可能会出现可移植性问题.

• The AUTO_INCREMENT feature described in the article is non-standard and may give portability issues when moving to a different database.

如果 INSERT 被中止，序列中的一个数字仍然会被消耗，所以你最终可能会在序列中出现漏洞.如果这是不可接受的，请为主(代理)键使用自动生成的序列，并从该键添加一个单独的映射到官方"序列号，强制该表索引的唯一性.

If an INSERT is aborted, a number from the sequence is consumed still, so you may end up with holes in the sequence. If that is unacceptable, use an autogenerated sequence for the primary (surrogate) key, and add a separate map from that key to the "official" sequence number, enforcing uniqueness in the index of that table.

另一种方法是在数据库中强制UNIQUEness，使用适当的TRANSACTION ISOLATION LEVEL 并添加应用程序逻辑来处理INSERT 的失败.

The alternative is to enforce UNIQUEness in the database, use an appropriate TRANSACTION ISOLATION LEVEL and add application logic to handle failure to INSERT.

这篇关于避免在mysql中并发插入多个相同的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！