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>