Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit dafaa3e

Browse filesBrowse files
hlinnakahlinnaka
committed
Implement genuine serializable isolation level.
Until now, our Serializable mode has in fact been what's called Snapshot Isolation, which allows some anomalies that could not occur in any serialized ordering of the transactions. This patch fixes that using a method called Serializable Snapshot Isolation, based on research papers by Michael J. Cahill (see README-SSI for full references). In Serializable Snapshot Isolation, transactions run like they do in Snapshot Isolation, but a predicate lock manager observes the reads and writes performed and aborts transactions if it detects that an anomaly might occur. This method produces some false positives, ie. it sometimes aborts transactions even though there is no anomaly. To track reads we implement predicate locking, see storage/lmgr/predicate.c. Whenever a tuple is read, a predicate lock is acquired on the tuple. Shared memory is finite, so when a transaction takes many tuple-level locks on a page, the locks are promoted to a single page-level lock, and further to a single relation level lock if necessary. To lock key values with no matching tuple, a sequential scan always takes a relation-level lock, and an index scan acquires a page-level lock that covers the search key, whether or not there are any matching keys at the moment. A predicate lock doesn't conflict with any regular locks or with another predicate locks in the normal sense. They're only used by the predicate lock manager to detect the danger of anomalies. Only serializable transactions participate in predicate locking, so there should be no extra overhead for for other transactions. Predicate locks can't be released at commit, but must be remembered until all the transactions that overlapped with it have completed. That means that we need to remember an unbounded amount of predicate locks, so we apply a lossy but conservative method of tracking locks for committed transactions. If we run short of shared memory, we overflow to a new "pg_serial" SLRU pool. We don't currently allow Serializable transactions in Hot Standby mode. That would be hard, because even read-only transactions can cause anomalies that wouldn't otherwise occur. Serializable isolation mode now means the new fully serializable level. Repeatable Read gives you the old Snapshot Isolation level that we have always had. Kevin Grittner and Dan Ports, reviewed by Jeff Davis, Heikki Linnakangas and Anssi Kääriäinen
1 parent c18f51d commit dafaa3e
Copy full SHA for dafaa3e

File tree

Expand file treeCollapse file tree

90 files changed

+14994
-270
lines changed
Filter options

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.
Dismiss banner
Expand file treeCollapse file tree

90 files changed

+14994
-270
lines changed

‎doc/src/sgml/catalogs.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/catalogs.sgml
+8-1Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -490,6 +490,13 @@
490490
<entry>Can an index of this type be clustered on?</entry>
491491
</row>
492492

493+
<row>
494+
<entry><structfield>ampredlocks</structfield></entry>
495+
<entry><type>bool</type></entry>
496+
<entry></entry>
497+
<entry>Does an index of this type manage fine-grained predicate locks?</entry>
498+
</row>
499+
493500
<row>
494501
<entry><structfield>amkeytype</structfield></entry>
495502
<entry><type>oid</type></entry>
@@ -6577,7 +6584,7 @@
65776584
<entry><type>text</type></entry>
65786585
<entry></entry>
65796586
<entry>Name of the lock mode held or desired by this process (see <xref
6580-
linkend="locking-tables">)</entry>
6587+
linkend="locking-tables"> and <xref linkend="xact-serializable">)</entry>
65816588
</row>
65826589
<row>
65836590
<entry><structfield>granted</structfield></entry>

‎doc/src/sgml/config.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/config.sgml
+70Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4456,6 +4456,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
44564456
<varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
44574457
<indexterm>
44584458
<primary>transaction isolation level</primary>
4459+
<secondary>setting default</secondary>
44594460
</indexterm>
44604461
<indexterm>
44614462
<primary><varname>default_transaction_isolation</> configuration parameter</primary>
@@ -4481,6 +4482,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
44814482
<varlistentry id="guc-default-transaction-read-only" xreflabel="default_transaction_read_only">
44824483
<indexterm>
44834484
<primary>read-only transaction</primary>
4485+
<secondary>setting default</secondary>
44844486
</indexterm>
44854487
<indexterm>
44864488
<primary><varname>default_transaction_read_only</> configuration parameter</primary>
@@ -4500,6 +4502,41 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
45004502
</listitem>
45014503
</varlistentry>
45024504

4505+
<varlistentry id="guc-default-transaction-deferrable" xreflabel="default_transaction_deferrable">
4506+
<indexterm>
4507+
<primary>deferrable transaction</primary>
4508+
<secondary>setting default</secondary>
4509+
</indexterm>
4510+
<indexterm>
4511+
<primary><varname>default_transaction_deferrable</> configuration parameter</primary>
4512+
</indexterm>
4513+
4514+
<term><varname>default_transaction_deferrable</varname> (<type>boolean</type>)</term>
4515+
<listitem>
4516+
<para>
4517+
When running at the <literal>serializable</> isolation level,
4518+
a deferrable read-only SQL transaction may be delayed before
4519+
it is allowed to proceed. However, once it begins executing
4520+
it does not incur any of the overhead required to ensure
4521+
serializability; so serialization code will have no reason to
4522+
force it to abort because of concurrent updates, making this
4523+
option suitable for long-running read-only transactions.
4524+
</para>
4525+
4526+
<para>
4527+
This parameter controls the default deferrable status of each
4528+
new transaction. It currently has no effect on read-write
4529+
transactions or those operating at isolation levels lower
4530+
than <literal>serializable</>. The default is <literal>off</>.
4531+
</para>
4532+
4533+
<para>
4534+
Consult <xref linkend="sql-set-transaction"> for more information.
4535+
</para>
4536+
</listitem>
4537+
</varlistentry>
4538+
4539+
45034540
<varlistentry id="guc-session-replication-role" xreflabel="session_replication_role">
45044541
<term><varname>session_replication_role</varname> (<type>enum</type>)</term>
45054542
<indexterm>
@@ -5125,6 +5162,39 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
51255162
</listitem>
51265163
</varlistentry>
51275164

5165+
<varlistentry id="guc-max-predicate-locks-per-transaction" xreflabel="max_predicate_locks_per_transaction">
5166+
<term><varname>max_predicate_locks_per_transaction</varname> (<type>integer</type>)</term>
5167+
<indexterm>
5168+
<primary><varname>max_predicate_locks_per_transaction</> configuration parameter</primary>
5169+
</indexterm>
5170+
<listitem>
5171+
<para>
5172+
The shared predicate lock table tracks locks on
5173+
<varname>max_predicate_locks_per_transaction</varname> * (<xref
5174+
linkend="guc-max-connections"> + <xref
5175+
linkend="guc-max-prepared-transactions">) objects (e.g., tables);
5176+
hence, no more than this many distinct objects can be locked at
5177+
any one time. This parameter controls the average number of object
5178+
locks allocated for each transaction; individual transactions
5179+
can lock more objects as long as the locks of all transactions
5180+
fit in the lock table. This is <emphasis>not</> the number of
5181+
rows that can be locked; that value is unlimited. The default,
5182+
64, has generally been sufficient in testing, but you might need to
5183+
raise this value if you have clients that touch many different
5184+
tables in a single serializable transaction. This parameter can
5185+
only be set at server start.
5186+
</para>
5187+
5188+
<para>
5189+
Increasing this parameter might cause <productname>PostgreSQL</>
5190+
to request more <systemitem class="osname">System V</> shared
5191+
memory than your operating system's default configuration
5192+
allows. See <xref linkend="sysvipc"> for information on how to
5193+
adjust those parameters, if necessary.
5194+
</para>
5195+
</listitem>
5196+
</varlistentry>
5197+
51285198
</variablelist>
51295199
</sect1>
51305200

‎doc/src/sgml/high-availability.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/high-availability.sgml
+9Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1916,6 +1916,15 @@ LOG: database system is ready to accept read only connections
19161916
your setting of <varname>max_prepared_transactions</> is 0.
19171917
</para>
19181918
</listitem>
1919+
<listitem>
1920+
<para>
1921+
The Serializable transaction isolation level is not yet available in hot
1922+
standby. (See <xref linkend="xact-serializable"> and
1923+
<xref linkend="serializable-consistency"> for details.)
1924+
An attempt to set a transaction to the serializable isolation level in
1925+
hot standby mode will generate an error.
1926+
</para>
1927+
</listitem>
19191928
</itemizedlist>
19201929

19211930
</para>

‎doc/src/sgml/indexam.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/indexam.sgml
+13Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -705,6 +705,19 @@ amrestrpos (IndexScanDesc scan);
705705
it is only safe to use such scans with MVCC-compliant snapshots.
706706
</para>
707707

708+
<para>
709+
When the <structfield>ampredlocks</> flag is not set, any scan using that
710+
index access method within a serializable transaction will acquire a
711+
non-blocking predicate lock on the full index. This will generate a
712+
read-write conflict with the insert of any tuple into that index by a
713+
concurrent serializable transaction. If certain patterns of read-write
714+
conflicts are detected among a set of concurrent serializable
715+
transactions, one of those transactions may be cancelled to protect data
716+
integrity. When the flag is set, it indicates that the index access
717+
method implements finer-grained predicate locking, which will tend to
718+
reduce the frequency of such transaction cancellations.
719+
</para>
720+
708721
</sect1>
709722

710723
<sect1 id="index-unique-checks">

‎doc/src/sgml/lobj.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/lobj.sgml
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -256,7 +256,7 @@ int lo_open(PGconn *conn, Oid lobjId, int mode);
256256
from a descriptor opened with <symbol>INV_WRITE</symbol> returns
257257
data that reflects all writes of other committed transactions as well
258258
as writes of the current transaction. This is similar to the behavior
259-
of <literal>SERIALIZABLE</> versus <literal>READ COMMITTED</> transaction
259+
of <literal>REPEATABLE READ</> versus <literal>READ COMMITTED</> transaction
260260
modes for ordinary SQL <command>SELECT</> commands.
261261
</para>
262262

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.