mysql模拟sequence

模拟oracle中sequence实现
新建查询

1、创建表tb_sequence，用来存放sequence值：

create table tb_sequence(name varchar(50) not null,current_value int not null,_increment int not null default 1, primary key(name));  

2、手动插入数据：

insert into tb_sequence values('userid',100,2);  

3、定义函数 _nextval：

 create function _nextval(n varchar(50)) returns integer   
begin  
declare _cur int;  
set _cur=(select current_value from tb_sequence where name= n);  
update tb_sequence  
 set current_value = _cur + _increment  
 where name=n ;  
return _cur;  
end;  

4、检验结果
select _nextval(‘userid’);
mybatis调用形式：
statementType=”CALLABLE”定义为函数调用，mode=IN为输入，mode=OUT为输出结果

  <!-- 定义函数 -->
  <select id="callFunction" statementType="CALLABLE">
          {
              #{p2,mode=OUT,jdbcType=NUMERIC}=call fun_add(
                  #{p1,mode=IN,jdbcType=VARCHAR}
              )
          }
  </select>

 <select id="updateSequence" statementType="CALLABLE"  resultType="java.lang.Integer">

        select  _nextval(#{name}) from dual

  </select>

加上排他锁：

create function `_nextval_safe`(n varchar(64))
 return int(11)
begin
    declare _cur int;  

    select current_value into _cur from tb_sequence where name= n for update;
    update tb_sequence  
    set current_value = _cur + _increment  
    where name=n ;  

    return _cur;
end;

PS:
ACID:
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全都不执行；
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态；
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行；
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

mysql锁机制分为表级锁和行级锁，本文就和大家分享一下我对mysql中行级锁中的共享锁与排他锁进行分享交流。

页级:引擎 BDB。
表级:引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行
行级:引擎 INNODB ， 单独的一行记录加锁
表级，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
行级,，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

InnoDB有两种模式的行锁：
共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据，对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

明确指定主键，并且有此笔资料，row lock

SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

无主键，table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

用过oracle的人都知道，orale没有类似mysql的AUTO_INCREMENT这样的自增长字段，实现插入一条记录，自动增加1.oracle是通过sequence（序列）来完成的。这样看起来，似乎mysql的自增长要比oracle序列的实现更好更方便。那我为什么还要提序列呢？有必要吗？

（1）首先mysql的自增长“序列”和序列是两回事，mysql本身不提供序列机制。
（2）mysql的AUTO_INCREMENT可以设置起始值，但是不能设置步长，其步长默认就是1.
（3）mysql一个表只能有一个自增长字段。自增长只能被分配给固定表的固定的某一字段，不能被多个表共用。并且只能是数字型。
（4）在历史表和数据迁移时，经常会遇到自增主键重复的问题。
（5）自增主键往往是没意义的。

在下列情况下可能你需要使用序列：

（1）业务复杂，你需要高度定制和控制主键（自增主键只能是按数字递增的，但是序列可以随心所欲的变化，比如按照年月日生成主键。。。）
（2）你希望手工维护自增长，方便数据迁移；
（3）当事务跨多表时，期望事务可靠性；
（4）当你需要一个业务上有意义的主键时，比如单据号等（若你需要的只是一个流水号，那么用自增更方便）；
（5）当你的主键很明确地需要和其他表关联时（若你的表不与其他表关联时，如日志表，自增更方便）
（6）你期望这个主键是唯一的，不需要重复利用的。

当然，序列也有缺点，主要就是程序处理麻烦，不如自增方便。oracle的自增有缓存，不用担心效率问题，而mysql只能通过触发器模拟，可能会有性能损失。 

原文：http://www.cnblogs.com/husam/p/5333568.html