PostgreSQL 无缝自增ID的实现 - by advisory lock

digoal

2016-10-20

postgresql , advisory lock , 无缝自增id

一般来说，数据库都会有序列的功能，例如postgresql就支持序列。

序列是指一直增长的值，但是它有一个不好的地方，就是用掉后就不会再有了，因此对于使用者来说，可能会拿到空洞的值。

例如

序列的值只要被获取后，就消耗掉了，一直往前。

所以如果中间出现过回滚，就会出现空洞。

那么有没有一种方法可以得到完全无缝的自增序列值呢？

本文将给大家提供一种方法。

postgresql提供了一个很棒的特性，叫做advisory lock，使用这个锁，可以提供并发的生成能力。

这个锁分为会话锁和事务锁，详见

<a href="https://www.postgresql.org/docs/9.6/static/functions-admin.html#functions-advisory-locks">https://www.postgresql.org/docs/9.6/static/functions-admin.html#functions-advisory-locks</a>

name

return type

description

pg_advisory_lock(key bigint)

void

obtain exclusive session level advisory lock

pg_advisory_lock(key1 int, key2 int)

pg_advisory_lock_shared(key bigint)

obtain shared session level advisory lock

pg_advisory_lock_shared(key1 int, key2 int)

pg_advisory_unlock(key bigint)

boolean

release an exclusive session level advisory lock

pg_advisory_unlock(key1 int, key2 int)

pg_advisory_unlock_all()

release all session level advisory locks held by the current session

pg_advisory_unlock_shared(key bigint)

release a shared session level advisory lock

pg_advisory_unlock_shared(key1 int, key2 int)

pg_advisory_xact_lock(key bigint)

obtain exclusive transaction level advisory lock

pg_advisory_xact_lock(key1 int, key2 int)

pg_advisory_xact_lock_shared(key bigint)

obtain shared transaction level advisory lock

pg_advisory_xact_lock_shared(key1 int, key2 int)

pg_try_advisory_lock(key bigint)

obtain exclusive session level advisory lock if available

pg_try_advisory_lock(key1 int, key2 int)

pg_try_advisory_lock_shared(key bigint)

obtain shared session level advisory lock if available

pg_try_advisory_lock_shared(key1 int, key2 int) boolean

pg_try_advisory_xact_lock(key bigint)

obtain exclusive transaction level advisory lock if available

pg_try_advisory_xact_lock(key1 int, key2 int)

pg_try_advisory_xact_lock_shared(key bigint)

obtain shared transaction level advisory lock if available

pg_try_advisory_xact_lock_shared(key1 int, key2 int)

将逻辑放到函数中，如下，需要保证id字段的唯一，以及它的顺序绝对保证，插入数据后，返回id值给客户端。

并行压测

性能还不错。

验证

<a href="http://info.flagcounter.com/h9v1">count</a>