Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the role of commit log in PostgreSQL". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the role of commit log in PostgreSQL"?

Concurrency Control concurrency control is a mechanism that maintains consistency (Consistency) and isolation (Isolation) when multiple transactions occur concurrently. Consistency and isolation are the C and I of database transaction ACID (Atomicity, Consistency, Isolation, Durability) attributes.

Multi-version concurrency control (MVCC) is a widely used concurrency control technology, and its main advantage is that read does not block write, and write does not block read. There are many variants of MVCC, and PostgreSQL uses a MVCC variant called Snapshot isolation Snapshot Isolation (SI) for concurrency control.

In MVCC, each DML operation creates a new version of the data, including Index, while retaining the previous version. When a transaction reads data, select one of the "correct" versions to ensure isolation between transactions.

After the transaction rollback is mentioned in the chapter Multi Version Heap Tuple, the newly written data is still stored in heap. How does PostgreSQL identify the status of the transaction that produced the data (commit / rollback / in progress) to determine which tuple is invisible? PG stores the status of each transaction through clog (commit log), and when the database starts up, the clog file is loaded into shared memory Checkpoint flushes the transaction status information in shared memory to disk.

I. basic concepts

Transaction ID

When a transaction is opened, the PostgreSQL transaction manager assigns a unique transaction ID (txid, unsigned 32bit integer) to the transaction.

The current transaction number can be obtained through the txid_current () function.

Testdb=# begin;BEGINtestdb=# select txid_current (); txid_current-2308 (1 row) testdb=#

In PG, the following txid is reserved for the system:

Txid = 0, which means Invalid txid, which is used to judge the validity of txid

Txid = 1, which means Bootstrap txid, used when intidb initializes the database

Txid = 2, which means Frozen txid, which is used when the transaction ID is rolled back and processed by the vacuum process

Transaction status

PostgreSQL defines four transaction states, namely IN_PROGRESS (in progress), COMMITTED (committed), ABORTED (rolled back), and SUB_COMMITTED (sub-transaction committed).

/ * * Possible transaction statuses-note that all-zeroes is the initial * state. * possible transaction states-Note that the initial states are all ASCII 0 * * A "subcommitted" transaction is a committed subtransaction whose parent * hasn't committed or aborted yet. A "subcommitted" transaction is a committed child transaction whose parent transaction has not been committed or rolled back. * / typedef int XidStatus;#define TRANSACTION_STATUS_IN_PROGRESS 0x00#define TRANSACTION_STATUS_COMMITTED 0x01#define TRANSACTION_STATUS_ABORTED 0x02#define TRANSACTION_STATUS_SUB_COMMITTED 0x03 II, clog file

Physical file

The clog file is stored in the $PGDATA/pg_xact directory

[xdb@localhost pg_xact] $lltotal 8 RWMI. 1 xdb xdb 8192 Jan 8 15:55 0000 [xdb@localhost pg_xact] $

Clog segment

As mentioned earlier, the transaction number is an unsigned 32bit integer, and PG logically divides clog into N segment through the following formula:

N = 0xFFFFFFFF/CLOG_XACTS_PER_PAGE/SLRU_PAGES_PER_SEGMENT

Where:

CLOG_XACTS_PER_PAGE is defined as

/ * We need two bits per xact, so four xacts fit in a byte * / # define CLOG_BITS_PER_XACT 2-- > each transaction state is represented by 2bits # define CLOG_XACTS_PER_BYTE 4-> each Byte can store 4 transaction states # define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)-> each page can store 8192 transaction states

That is, CLOG_XACTS_PER_PAGE = 8192

SLRU_PAGES_PER_SEGMENT is defined as

# define SLRU_PAGES_PER_SEGMENT 32

Replace it in the formula:

N = 0xFFFFFFFF/CLOG_XACTS_PER_PAGE/SLRU_PAGES_PER_SEGMENT

= 0xFFFFFFFF/ (8192 / 4) / 32

= 4096

Physically, each segment has 32 Pages (SLRU_PAGES_PER_SEGMENT = 32), then each segment file size is 8K*32=256K.

3. Txid & clog

Given a transaction number, how do I get the status of the transaction?

PG first obtains the page in which the transaction state is stored in the clog (that is, pageno) through the transaction number, and then locates the offset of the Byte storing the transaction state in the page and the offset in the Byte.

# define TransactionIdToPage (xid) (xid) / (TransactionId) CLOG_XACTS_PER_PAGE) # define TransactionIdToPgIndex (xid) ((xid)% (TransactionId) CLOG_XACTS_PER_PAGE) # define TransactionIdToByte (xid) (TransactionIdToPgIndex (xid) / CLOG_XACTS_PER_BYTE) # define TransactionIdToBIndex (xid) ((xid)% (TransactionId) CLOG_XACTS_PER_BYTE)

Given transaction number 2308, according to the above formula, you can get:

Page = 2308 / (8192 / 4) = 0-> No. 0 page

PageIndex = 2308 (8192 / 4) = 2308-> Page inner offset

ByteInPage = 2308 / 4 = 577-> the 577th Byte in this Page

ByteIndex = 2304 4 = 0-> the first 2bits in the byte

The following is verified by an actual case

Open a transaction

Testdb=# begin;BEGINtestdb=# select txid_current (); txid_current-2308 (1 row)

View clog

[xdb@localhost pg_xact] $hexdump-C. / 0000-s 577-n 100000241 00 |. | 00000242

0x00-> TRANSACTION_STATUS_IN_PROGRESS

Commit transaction

Execute checkpoint and refresh to disk

Testdb=# commit;COMMITtestdb=# checkpoint;CHECKPOINT

View clog

[xdb@localhost pg_xact] $hexdump-C. / 0000-s 577-n 100000241 01 |. | 00000242

The value is 0x01-> TRANSACTION_STATUS_COMMITTED

Reopen a transaction 2309, roll back the transaction, and the value in clog should be 0x09 (binary value: 0000 1001)

Testdb=# begin;BEGINtestdb=# select txid_current (); txid_current-2309 (1 row) testdb=# select 2308% 4;? column?-0 (1 row) testdb=# rollback;ROLLBACKtestdb=# checkpoint;CHECKPOINTtestdb=#

The content in the clog file is 0x09, as expected.

[xdb@localhost pg_xact] $hexdump-C. / 0000-s 577-n 100000241 09 |. | 00000242 [xdb@localhost pg_xact] $so far, I believe you have a better understanding of "what is the role of commit log in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.