利用mysql事务特性实现并发安全的自增ID示例

项目中经常会用到自增id，比如uid，最简单的方法就是用直接用数据库提供的AUTO_INCREMENT，但是如果用户量非常大，几千万，几亿然后需要分表存储的时候呢，这种方案就搞不定了，所以最好有一个全局的自增ID的生成器，不管是否分表，都能从生成器中获取到全局自增的ID。

实现方法应该有很多，不过所有的方案都需要解决一个问题，就是保证在高并发的情景下，数据获取依然正确，每次获取的ID都不会重复。

这里我分享两种利用mysql的innodb的事务特性来实现的方案，一种是实现过了的，另一种没有试验过，不过应该也能走的通。

先介绍第一种，在数据库中单独设置一张表，来存储ID，表有两个字段，一个是种类吧，一个就是ID：


代码如下:

CREATE TABLE auto_id(
idname varchar(20) NOT NULL DEFAULT '',
id bigint(20) NOT NULL DEFAULT 0 COMMENT '',
primary key(idname)
)ENGINE=Innodb DEFAULT CHARSET=utf8;

接下来是一个存储过程：


代码如下:

delimiter //
drop procedure if exists get_increment_id;
create procedure get_increment_id(in idname_in varchar(20), in small_in bigint, out id_out bigint)
begin
declare oldid bigint;
start transaction;
select id into oldid from maibo_auto_id where idname=idname_in for update;
if oldid is NULL then
insert into maibo_auto_id(idname,id) value(idname_in, small_in);
set id_out=small_in;
else
update maibo_auto_id set id=id+1 where idname=idname_in;
set id_out=oldid+1;
end if;
commit;
end;
//

重点是这句，select id into oldid from maibo_auto_id where idname=idname_in for update，会给相关数据加一个独占锁定，这时候别的进程如果来读取该条记录，就会进入等待，等待这个进程commit之后，再继续，这样就保证了在并发的情况下，不同的进程不会取到相同的值。

如果你的前端是用php实现的。

只需执行如下两个sql，就可以获取到，这个small参数是定义的是从多少开始自增


代码如下:

$sql = "call get_increment_id('{$key}', {$small}, @id)";
$ret = $db->getData("select @id");

还有另外一种方法，就是利用mysql的auto_increment。

先创建一张表，表里边只有一个自增字段：


代码如下:

create table test(
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
primary key (id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

通过如下两条sql：


代码如下:

UPDATE test SET id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();

也能解决问题， LAST_INSERT_ID是不用查表的，而且只针对当前连接，也就是说别的连接的更新不会影响到当前连接的取值。

这样可能每个ID都得弄一张表来维护，这也是缺点。

具体使用中如何处理，就看自己的选择了。

赞 (0)

相关推荐

  • MYSQL锁表问题的解决方法

    本文实例讲述了MYSQL锁表问题的解决方法.分享给大家供大家参考,具体如下: 很多时候!一不小心就锁表!这里讲解决锁表终极方法! 案例一 mysql>show processlist; 参看sql语句 一般少的话 mysql>kill thread_id; 就可以解决了 kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, 简单的脚本如下. #!/bin/bash mysql - u root - e " show processli

  • mysql锁表和解锁语句分享

    页级的典型代表引擎为BDB. 表级的典型代表引擎为MyISAM,MEMORY以及很久以前的ISAM. 行级的典型代表引擎为INNODB. -我们实际应用中用的最多的就是行锁. 行级锁的优点如下: 1).当很多连接分别进行不同的查询时减小LOCK状态. 2).如果出现异常,可以减少数据的丢失.因为一次可以只回滚一行或者几行少量的数据. 行级锁的缺点如下: 1).比页级锁和表级锁要占用更多的内存. 2).进行查询时比页级锁和表级锁需要的I/O要多,所以我们经常把行级锁用在写操作而不是读操作. 3).

  • MySQL中SELECT+UPDATE处理并发更新问题解决方案分享

    MySQL中SELECT+UPDATE处理并发更新问题解决方案分享

    问题背景: 假设MySQL数据库有一张会员表vip_member(InnoDB表),结构如下:   当一个会员想续买会员(只能续买1个月.3个月或6个月)时,必须满足以下业务要求: •如果end_at早于当前时间,则设置start_at为当前时间,end_at为当前时间加上续买的月数 •如果end_at等于或晚于当前时间,则设置end_at=end_at+续买的月数 •续买后active_status必须为1(即被激活) 问题分析: 对于上面这种情况,我们一般会先SELECT查出这条记录,然后根

  • php并发对MYSQL造成压力的解决方法

    PHP页面有一个浏览量统计每秒有200并发,造成insert给mysql造成很大压力 lnmp环境,其他页面内容都做了缓存了,没有多大的负载,就是这个浏览量统计功能,给mysql带来不小的压力,请问诸位有什么解决办法吗? 怎么都说不要用MySQL来做,每秒200并发对MySQL来说不算啥难事啊.而且换成Redis.Memcached,持久化姑且不说,业务代码和运维部署量都不小. 我给你几个建议,尽量让你的运维部署和业务代码改动小一些. 你可以做主从分离,不要在一个库上高并发插入同时还做大量统计运

  • Yii 连接、修改 MySQL 数据库及phpunit 测试连接

    >>>database<<< 1. 修改 protected/config/main.php 去掉mysql数据库连接方式的注释,并且修改用户名,密码以及连接的数据库. 2. 新建 protected/tests/unit/DbTest.php 内容如下: <?php class DbTest extends CTestCase { public function testConnection() { $this->assertNotEquals(NULL,

  • mysql 锁表锁行语句分享(MySQL事务处理)

    复制代码 代码如下: mysql_query("set autocommit=0"); $list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE"); $db->query("DELETE from prizes WHERE id =".$list_one['id']); mysql_query("co

  • 深入mysql并发插入优化详解

    使用storm处理日志的时候,经常会遇到并发插入mysql的效率问题,到网上查了些资料,做一下笔记将表的引擎改为 myisam, 修改 my.cnf 的concurrent_insert=2,concurrent_insert 可以设的值有 0 1 2 ,2 是完全支持并发插入 1) concurrent _insert =0 ,无论MyISAM的表数据文件中间是否存在因为删除而留下俄空闲空间,都不允许concurrent insert. 2)concurrent_insert = 1,是当My

  • Yii+MYSQL锁表防止并发情况下重复数据的方法

    本文实例讲述了Yii+MYSQL锁表防止并发情况下重复数据的方法.分享给大家供大家参考,具体如下: lock table 读锁定 如果一个线程获得在一个表上的read锁,那么该线程和所有其他线程只能从表中读数据,不能进行任何写操作. lock tables user read;//读锁定表 unlock tables;//解锁 lock tables user read local;//本地读锁定表,其他线程的insert未被阻塞,update操作被阻塞 lock table 写锁定 如果一个线

  • Yii实现MySQL多数据库和读写分离实例分析

    本文实例分析了Yii实现MySQL多数据库和读写分离的方法.分享给大家供大家参考.具体分析如下: Yii Framework是一个基于组件.用于开发大型 Web 应用的高性能 PHP 框架.Yii提供了今日Web 2.0应用开发所需要的几乎一切功能,也是最强大的框架之一,下文我们来介绍Yii实现MySQL多库和读写分离的方法 前段时间为SNS产品做了架构设计,在程序框架方面做了不少相关的压力测试,最终选定了YiiFramework,至于为什么没选用公司内部的 PHP框架,其实理由很充分,公司的框

  • MySQL中实现高性能高并发计数器方案（例如文章点击数）

    现在有很多的项目,对计数器的实现甚是随意,比如在实现网站文章点击数的时候,是这么设计数据表的,如:"article_id, article_name, article_content, article_author, article_view--在article_view中记录该文章的浏览量.诈一看似乎没有问题.对于小站,比如本博客,就是这么做的,因为小菜的博客难道会涉及并发问题吗?答案显而易见,一天没多少IP,而且以后不会很大. 言归正传,对文章资讯类为主的项目,在浏览一个页面的时候不但要进行

  • mysql 队列 实现并发读

    一个 MySQL 表可以看作是一个队列,每一行为一个元素.每次查询得到满足某个条件的最前面的一行,并将它从表中删除或者改变它的状态,使得下次查询不会得到它.在没有并发访问的情况下,简单地用 SELECT 得到一行,再用UPDATE(或者DELETE)语句修改之,就可以实现. 复制代码 代码如下: SELECT * FROM targets WHERE status='C' LIMIT 1; UPDATE targets SET status='D' WHERE id='id'; 如果有并发访问,

随机推荐