標籤：並發控制 資料庫 鎖 advisory鎖 lock hash
並發控制是DBMS的關鍵技術，並發控制技術也稱為同步機制，其實現通常依賴於底層的並發控制機制。作業系統提供了多種同步對象，如事件 Event、互斥鎖 Mutex和條件變數 Cond、訊號量Semaphore、讀寫鎖 RWLock、自旋鎖 Spinlock等。資料庫管理系統自己實現封鎖主要是考慮：
鎖語義加強：OS只提供排它鎖。為了提高並發度，資料庫至少需要共用鎖定和排它鎖，即讀鎖和寫鎖；
鎖的功能增強：資料庫提供視圖監測封鎖情況和進行死結檢測；
可靠性的考慮：例如，在某個持鎖進程任意一點復原時，資料庫系統要能夠釋放其佔有的鎖；
可移植性的考慮：各個平台的封鎖並沒有完全對等的一一匹配；
效率的考慮；例如使用spinlock來提高在SMP處理機上的效能；
但是最終，資料庫系統都是依靠作業系統和硬體平台提供的同步機制來實現自己的封鎖。pg中提供了自旋鎖、輕量鎖以及常規鎖。自旋鎖和輕量鎖都是pg內部使用的。常規鎖pg內部使用，使用者也可以通過select * for update等指定鎖。
先把pg系統核心裡鎖相關的關鍵結構和變數吧。
自旋鎖：
用於鎖鎖或其它記憶體結構，對使用者透明，不支援死結檢測。
pg中實現自旋鎖的幾個相關檔案是spin.h, spin.c, s_lock.h, s_lock.c。
spin.h 定義了自旋鎖的介面函數/宏。
spin.c 定義了一個利用訊號量實現的自旋鎖，如果目標平台沒有自己的自旋鎖就用這個。
s_lock.h 定義了硬體、作業系統平台相關的自旋鎖的實現。
自旋鎖在少數幾個地方被直接使用，它主要是作為實現輕量鎖的一個手段。每個LWLOCK都需要有一個自旋鎖。
輕量鎖：
輕量鎖有讀鎖（LW_SHARED）和寫鎖（LW_EXCLUSIVE），對使用者透明，不支援死結檢測，用於鎖記憶體結構，保持臨界區進程間互斥，共用記憶體中分配控制結構的空間。
主要介面函數在 lwlock.h 檔案中定義。下面是域定義的輕量鎖。
/*
* We have a number of predefined LWLocks, plus a bunch of LWLocks that are
* dynamically assigned (e.g., for shared buffers). The LWLock structures
* live in shared memory (since they contain shared data) and are identified
* by values of this enumerated type. We abuse the notion of an enum somewhat
* by allowing values not listed in the enum declaration to be assigned.
* The extra value MaxDynamicLWLock is there to keep the compiler from
* deciding that the enum can be represented as char or short ...
*
* If you remove a lock, please replace it with a placeholder. This retains
* the lock numbering, which is helpful for DTrace and other external
* debugging scripts.
*/
typedef enum LWLockId
{
BufFreelistLock,
ShmemIndexLock,
OidGenLock,
XidGenLock,
ProcArrayLock,
SInvalReadLock,
SInvalWriteLock,
WALInsertLock,
WALWriteLock,
ControlFileLock,
CheckpointLock,
CLogControlLock,
SubtransControlLock,
MultiXactGenLock,
MultiXactOffsetControlLock,
MultiXactMemberControlLock,
RelCacheInitLock,
CheckpointerCommLock,
TwoPhaseStateLock,
TablespaceCreateLock,
BtreeVacuumLock,
AddinShmemInitLock,
AutovacuumLock,
AutovacuumScheduleLock,
SyncScanLock,
RelationMappingLock,
AsyncCtlLock,
AsyncQueueLock,
SerializableXactHashLock,
SerializableFinishedListLock,
SerializablePredicateLockListLock,
OldSerXidLock,
SyncRepLock,
/* Individual lock IDs end here */
FirstBufMappingLock,
FirstLockMgrLock = FirstBufMappingLock + NUM_BUFFER_PARTITIONS,
FirstPredicateLockMgrLock = FirstLockMgrLock + NUM_LOCK_PARTITIONS,
/* must be last except for MaxDynamicLWLock: */
NumFixedLWLocks = FirstPredicateLockMgrLock + NUM_PREDICATELOCK_PARTITIONS,
MaxDynamicLWLock = 1000000000
} LWLockId;
typedef enum LWLockMode
{
LW_EXCLUSIVE,
LW_SHARED,
LW_WAIT_UNTIL_FREE /* A special mode used in PGPROC->lwlockMode,
* when waiting for lock to become free. Not
* to be used as LWLockAcquire argument */
} LWLockMode;
pg系統中都有哪些輕量鎖呢？可以從src/backend/storage/lmgr/lwlock.c檔案中的方法CreateLWLocks()->NumLWLocks()查看，這個方法的作用是Allocate shmem space for LWLocks and initialize the locks。
int
NumLWLocks(void)
{
int numLocks;
/*
* Possibly this logic should be spread out among the affected modules,
* the same way that shmem space estimation is done. But for now, there
* are few enough users of LWLocks that we can get away with just keeping
* the knowledge here.
*/
/* Predefined LWLocks */
numLocks = (int) NumFixedLWLocks;
/* bufmgr.c needs two for each shared buffer */
numLocks += 2 * NBuffers;
/* proc.c needs one for each backend or auxiliary process */
numLocks += MaxBackends + NUM_AUXILIARY_PROCS;
/* clog.c needs one per CLOG buffer */
numLocks += CLOGShmemBuffers();
/* subtrans.c needs one per SubTrans buffer */
numLocks += NUM_SUBTRANS_BUFFERS;
/* multixact.c needs two SLRU areas */
numLocks += NUM_MXACTOFFSET_BUFFERS + NUM_MXACTMEMBER_BUFFERS;
/* async.c needs one per Async buffer */
numLocks += NUM_ASYNC_BUFFERS;
/* predicate.c needs one per old serializable xid buffer */
numLocks += NUM_OLDSERXID_BUFFERS;
/*
* Add any requested by loadable modules; for backwards-compatibility
* reasons, allocate at least NUM_USER_DEFINED_LWLOCKS of them even if
* there are no explicit requests.
*/
lock_addin_request_allowed = false;
numLocks += Max(lock_addin_request, NUM_USER_DEFINED_LWLOCKS);
return numLocks;
}
簡單前後相關過程可以參見我的博文《PostgreSQL啟動過程中的那些事七：初始化共用記憶體和訊號一：初始化shmemIndex和訊號 》
http://blog.csdn.net/beiigang/article/details/7163465
常規鎖（Lock）：
我們主要看常規鎖。常規鎖是資料庫特有的鎖機制，提供了豐富的鎖模式，可以自動進行死結檢測和解死結。實現要比前兩種複雜許多。
看看和常規鎖相關的幾個結構和變數，具體參見src/include/storage/lock.h：
LOCKMETHOD：封鎖方法，有兩種，如下：
/* These identify the known lock methods */
#define DEFAULT_LOCKMETHOD 1
#define USER_LOCKMETHOD 2
前者是系統內部加鎖，這個加解鎖過程對使用者是透明的，例如使用者在做SELECT時，系統就自動加AccessShareLock鎖防止修改表結構等並行作業。後者是使用者鎖，即使用者使用LOCK語句加的鎖，這種鎖需要使用者顯式地解鎖或者使用者出現事務故障自動解鎖。
LOCKMODE：鎖的模式，總共有8種。NoLock不算。
/*
* These are the valid values of type LOCKMODE for all the standard lock
* methods (both DEFAULT and USER).
*/
/* NoLock is not a lock mode, but a flag value meaning "don‘t get a lock" */
#define NoLock 0
#define AccessShareLock 1 /* SELECT */
#define RowShareLock 2 /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock 3 /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4 /* VACUUM (non-FULL),ANALYZE, CREATE INDEX CONCURRENTLY */
#define ShareLock 5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock 6 /* like EXCLUSIVE MODE, but allows ROW SHARE */
#define ExclusiveLock 7 /* blocks ROW SHARE/SELECT...FOR UPDATE */
#define AccessExclusiveLock 8 /* ALTER TABLE, DROP TABLE, VACUUM FULL, and unqualified LOCK TABLE */
LockTagType: 被鎖對象種類，也有使用者鎖和ADVISORY鎖（這個在pg9.1新增加了trasaction類型的，原來只有session類型的）
/*
* LOCKTAG is the key information needed to look up a LOCK item in the
* lock hashtable. A LOCKTAG value uniquely identifies a lockable object.
*
* The LockTagType enum defines the different kinds of objects we can lock.
* We can handle up to 256 different LockTagTypes.
*/
typedef enum LockTagType
{
LOCKTAG_RELATION, /* whole relation */
/* ID info for a relation is DB OID + REL OID; DB OID = 0 if shared */
LOCKTAG_RELATION_EXTEND, /* the right to extend a relation */
/* same ID info as RELATION */
LOCKTAG_PAGE, /* one page of a relation */
/* ID info for a page is RELATION info + BlockNumber */
LOCKTAG_TUPLE, /* one physical tuple */
/* ID info for a tuple is PAGE info + OffsetNumber */
LOCKTAG_TRANSACTION, /* transaction (for waiting for xact done) */
/* ID info for a transaction is its TransactionId */
LOCKTAG_VIRTUALTRANSACTION, /* virtual transaction (ditto) */
/* ID info for a virtual transaction is its VirtualTransactionId */
LOCKTAG_OBJECT, /* non-relation database object */
/* ID info for an object is DB OID + CLASS OID + OBJECT OID + SUBID */
/*
* Note: object ID has same representation as in pg_depend and
* pg_description, but notice that we are constraining SUBID to 16 bits.
* Also, we use DB OID = 0 for shared objects such as tablespaces.
*/
LOCKTAG_USERLOCK, /* reserved for old contrib/userlock code */
LOCKTAG_ADVISORY /* advisory user locks */
} LockTagType;
#define LOCKTAG_LAST_TYPE LOCKTAG_ADVISORY
LOCKTAG：被加鎖的對象的標識：
/*
* The LOCKTAG struct is defined with malice aforethought to fit into 16
* bytes with no padding. Note that this would need adjustment if we were
* to widen Oid, BlockNumber, or TransactionId to more than 32 bits.
*
* We include lockmethodid in the locktag so that a single hash table in
* shared memory can store locks of different lockmethods.
*/
typedef struct LOCKTAG
{
uint32 locktag_field1; /* a 32-bit ID field：用於標識資料庫dbid */
uint32 locktag_field2; /* a 32-bit ID field：用於標識關係relid */
uint32 locktag_field3; /* a 32-bit ID field：用於標識頁blocknum */
uint16 locktag_field4; /* a 16-bit ID field：用於標識行位移offnum */
uint8 locktag_type; /* see enum LockTagType */
uint8 locktag_lockmethodid; /* lockmethod indicator */
} LOCKTAG;
如LOCKTAG的注釋裡所說，常規鎖在pg系統裡是通過shared memory裡的hash表：常規鎖管理器——"LOCK hash"管理的，有興趣的可以參考我的博文《PostgreSQL啟動過程中的那些事七：初始化共用記憶體和訊號八：shmem中初始化常規鎖管理器 》
http://blog.csdn.net/beiigang/article/details/7299804
常規鎖機制可以參考pg的官方手冊，章節和內容見下面
13.3. Explicit Locking
http://www.postgresql.org/docs/9.4/static/explicit-locking.html
這節分為：表鎖、行鎖、頁鎖、死結、Advisory鎖（這個名字怎麼翻譯好？？？ 忠告鎖，公告鎖，諮詢鎖？？？）。
後面的內容提綱就是：
表鎖、
行鎖、
頁鎖、
死結、
Advisory鎖（這個名字怎麼翻譯好？？？ 忠告鎖，公告鎖，諮詢鎖？？？）、
查看鎖。
感覺有些累了，懶了，不想寫了，後面看情況到網上找些同仁同志同學朋友們的文章，放到這兒歸類，如有需要待下次按我的思路再整理組織，今次先把題目做完整吧。
這篇先到這兒吧。
-----------------
轉載請著明出處：
blog.csdn.net/beiigang
資料庫並發事務控制四：postgresql資料庫的鎖機制
