| 1 |
<!-- doc/src/sgml/mvcc.sgml --> |
| 2 |
|
| 3 |
<chapter id="mvcc"> |
| 4 |
<title>Concurrency Control</title> |
| 5 |
|
| 6 |
<indexterm> |
| 7 |
<primary>concurrency</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
This chapter describes the behavior of the |
| 12 |
<productname>PostgreSQL</productname> database system when two or |
| 13 |
more sessions try to access the same data at the same time. The |
| 14 |
goals in that situation are to allow efficient access for all |
| 15 |
sessions while maintaining strict data integrity. Every developer |
| 16 |
of database applications should be familiar with the topics covered |
| 17 |
in this chapter. |
| 18 |
</para> |
| 19 |
|
| 20 |
<sect1 id="mvcc-intro"> |
| 21 |
<title>Introduction</title> |
| 22 |
|
| 23 |
<indexterm> |
| 24 |
<primary>Multiversion Concurrency Control</primary> |
| 25 |
</indexterm> |
| 26 |
|
| 27 |
<indexterm> |
| 28 |
<primary>MVCC</primary> |
| 29 |
</indexterm> |
| 30 |
|
| 31 |
<indexterm> |
| 32 |
<primary>Serializable Snapshot Isolation</primary> |
| 33 |
</indexterm> |
| 34 |
|
| 35 |
<indexterm> |
| 36 |
<primary>SSI</primary> |
| 37 |
</indexterm> |
| 38 |
|
| 39 |
<para> |
| 40 |
<productname>PostgreSQL</productname> provides a rich set of tools |
| 41 |
for developers to manage concurrent access to data. Internally, |
| 42 |
data consistency is maintained by using a multiversion |
| 43 |
model (Multiversion Concurrency Control, <acronym>MVCC</acronym>). |
| 44 |
This means that each SQL statement sees |
| 45 |
a snapshot of data (a <firstterm>database version</firstterm>) |
| 46 |
as it was some |
| 47 |
time ago, regardless of the current state of the underlying data. |
| 48 |
This prevents statements from viewing inconsistent data produced |
| 49 |
by concurrent transactions performing updates on the same |
| 50 |
data rows, providing <firstterm>transaction isolation</firstterm> |
| 51 |
for each database session. <acronym>MVCC</acronym>, by eschewing |
| 52 |
the locking methodologies of traditional database systems, |
| 53 |
minimizes lock contention in order to allow for reasonable |
| 54 |
performance in multiuser environments. |
| 55 |
</para> |
| 56 |
|
| 57 |
<para> |
| 58 |
The main advantage of using the <acronym>MVCC</acronym> model of |
| 59 |
concurrency control rather than locking is that in |
| 60 |
<acronym>MVCC</acronym> locks acquired for querying (reading) data |
| 61 |
do not conflict with locks acquired for writing data, and so |
| 62 |
reading never blocks writing and writing never blocks reading. |
| 63 |
<productname>PostgreSQL</productname> maintains this guarantee |
| 64 |
even when providing the strictest level of transaction |
| 65 |
isolation through the use of an innovative <firstterm>Serializable |
| 66 |
Snapshot Isolation</firstterm> (<acronym>SSI</acronym>) level. |
| 67 |
</para> |
| 68 |
|
| 69 |
<para> |
| 70 |
Table- and row-level locking facilities are also available in |
| 71 |
<productname>PostgreSQL</productname> for applications which don't |
| 72 |
generally need full transaction isolation and prefer to explicitly |
| 73 |
manage particular points of conflict. However, proper |
| 74 |
use of <acronym>MVCC</acronym> will generally provide better |
| 75 |
performance than locks. In addition, application-defined advisory |
| 76 |
locks provide a mechanism for acquiring locks that are not tied |
| 77 |
to a single transaction. |
| 78 |
</para> |
| 79 |
</sect1> |
| 80 |
|
| 81 |
<sect1 id="transaction-iso"> |
| 82 |
<title>Transaction Isolation</title> |
| 83 |
|
| 84 |
<indexterm> |
| 85 |
<primary>transaction isolation</primary> |
| 86 |
</indexterm> |
| 87 |
|
| 88 |
<para> |
| 89 |
The <acronym>SQL</acronym> standard defines four levels of |
| 90 |
transaction isolation. The most strict is Serializable, |
| 91 |
which is defined by the standard in a paragraph which says that any |
| 92 |
concurrent execution of a set of Serializable transactions is guaranteed |
| 93 |
to produce the same effect as running them one at a time in some order. |
| 94 |
The other three levels are defined in terms of phenomena, resulting from |
| 95 |
interaction between concurrent transactions, which must not occur at |
| 96 |
each level. The standard notes that due to the definition of |
| 97 |
Serializable, none of these phenomena are possible at that level. (This |
| 98 |
is hardly surprising -- if the effect of the transactions must be |
| 99 |
consistent with having been run one at a time, how could you see any |
| 100 |
phenomena caused by interactions?) |
| 101 |
</para> |
| 102 |
|
| 103 |
<para> |
| 104 |
The phenomena which are prohibited at various levels are: |
| 105 |
|
| 106 |
<variablelist> |
| 107 |
<varlistentry> |
| 108 |
<term> |
| 109 |
dirty read |
| 110 |
<indexterm><primary>dirty read</primary></indexterm> |
| 111 |
</term> |
| 112 |
<listitem> |
| 113 |
<para> |
| 114 |
A transaction reads data written by a concurrent uncommitted transaction. |
| 115 |
</para> |
| 116 |
</listitem> |
| 117 |
</varlistentry> |
| 118 |
|
| 119 |
<varlistentry> |
| 120 |
<term> |
| 121 |
nonrepeatable read |
| 122 |
<indexterm><primary>nonrepeatable read</primary></indexterm> |
| 123 |
</term> |
| 124 |
<listitem> |
| 125 |
<para> |
| 126 |
A transaction re-reads data it has previously read and finds that data |
| 127 |
has been modified by another transaction (that committed since the |
| 128 |
initial read). |
| 129 |
</para> |
| 130 |
</listitem> |
| 131 |
</varlistentry> |
| 132 |
|
| 133 |
<varlistentry> |
| 134 |
<term> |
| 135 |
phantom read |
| 136 |
<indexterm><primary>phantom read</primary></indexterm> |
| 137 |
</term> |
| 138 |
<listitem> |
| 139 |
<para> |
| 140 |
A transaction re-executes a query returning a set of rows that satisfy a |
| 141 |
search condition and finds that the set of rows satisfying the condition |
| 142 |
has changed due to another recently-committed transaction. |
| 143 |
</para> |
| 144 |
</listitem> |
| 145 |
</varlistentry> |
| 146 |
|
| 147 |
<varlistentry> |
| 148 |
<term> |
| 149 |
serialization anomaly |
| 150 |
<indexterm><primary>serialization anomaly</primary></indexterm> |
| 151 |
</term> |
| 152 |
<listitem> |
| 153 |
<para> |
| 154 |
The result of successfully committing a group of transactions |
| 155 |
is inconsistent with all possible orderings of running those |
| 156 |
transactions one at a time. |
| 157 |
</para> |
| 158 |
</listitem> |
| 159 |
</varlistentry> |
| 160 |
</variablelist> |
| 161 |
</para> |
| 162 |
|
| 163 |
<para> |
| 164 |
<indexterm> |
| 165 |
<primary>transaction isolation level</primary> |
| 166 |
</indexterm> |
| 167 |
The SQL standard and PostgreSQL-implemented transaction isolation levels |
| 168 |
are described in <xref linkend="mvcc-isolevel-table"/>. |
| 169 |
</para> |
| 170 |
|
| 171 |
<table tocentry="1" id="mvcc-isolevel-table"> |
| 172 |
<title>Transaction Isolation Levels</title> |
| 173 |
<tgroup cols="5"> |
| 174 |
<thead> |
| 175 |
<row> |
| 176 |
<entry> |
| 177 |
Isolation Level |
| 178 |
</entry> |
| 179 |
<entry> |
| 180 |
Dirty Read |
| 181 |
</entry> |
| 182 |
<entry> |
| 183 |
Nonrepeatable Read |
| 184 |
</entry> |
| 185 |
<entry> |
| 186 |
Phantom Read |
| 187 |
</entry> |
| 188 |
<entry> |
| 189 |
Serialization Anomaly |
| 190 |
</entry> |
| 191 |
</row> |
| 192 |
</thead> |
| 193 |
<tbody> |
| 194 |
<row> |
| 195 |
<entry> |
| 196 |
Read uncommitted |
| 197 |
</entry> |
| 198 |
<entry> |
| 199 |
Allowed, but not in PG |
| 200 |
</entry> |
| 201 |
<entry> |
| 202 |
Possible |
| 203 |
</entry> |
| 204 |
<entry> |
| 205 |
Possible |
| 206 |
</entry> |
| 207 |
<entry> |
| 208 |
Possible |
| 209 |
</entry> |
| 210 |
</row> |
| 211 |
|
| 212 |
<row> |
| 213 |
<entry> |
| 214 |
Read committed |
| 215 |
</entry> |
| 216 |
<entry> |
| 217 |
Not possible |
| 218 |
</entry> |
| 219 |
<entry> |
| 220 |
Possible |
| 221 |
</entry> |
| 222 |
<entry> |
| 223 |
Possible |
| 224 |
</entry> |
| 225 |
<entry> |
| 226 |
Possible |
| 227 |
</entry> |
| 228 |
</row> |
| 229 |
|
| 230 |
<row> |
| 231 |
<entry> |
| 232 |
Repeatable read |
| 233 |
</entry> |
| 234 |
<entry> |
| 235 |
Not possible |
| 236 |
</entry> |
| 237 |
<entry> |
| 238 |
Not possible |
| 239 |
</entry> |
| 240 |
<entry> |
| 241 |
Allowed, but not in PG |
| 242 |
</entry> |
| 243 |
<entry> |
| 244 |
Possible |
| 245 |
</entry> |
| 246 |
</row> |
| 247 |
|
| 248 |
<row> |
| 249 |
<entry> |
| 250 |
Serializable |
| 251 |
</entry> |
| 252 |
<entry> |
| 253 |
Not possible |
| 254 |
</entry> |
| 255 |
<entry> |
| 256 |
Not possible |
| 257 |
</entry> |
| 258 |
<entry> |
| 259 |
Not possible |
| 260 |
</entry> |
| 261 |
<entry> |
| 262 |
Not possible |
| 263 |
</entry> |
| 264 |
</row> |
| 265 |
</tbody> |
| 266 |
</tgroup> |
| 267 |
</table> |
| 268 |
|
| 269 |
<para> |
| 270 |
In <productname>PostgreSQL</productname>, you can request any of |
| 271 |
the four standard transaction isolation levels, but internally only |
| 272 |
three distinct isolation levels are implemented, i.e., PostgreSQL's |
| 273 |
Read Uncommitted mode behaves like Read Committed. This is because |
| 274 |
it is the only sensible way to map the standard isolation levels to |
| 275 |
PostgreSQL's multiversion concurrency control architecture. |
| 276 |
</para> |
| 277 |
|
| 278 |
<para> |
| 279 |
The table also shows that PostgreSQL's Repeatable Read implementation |
| 280 |
does not allow phantom reads. This is acceptable under the SQL |
| 281 |
standard because the standard specifies which anomalies must |
| 282 |
<emphasis>not</emphasis> occur at certain isolation levels; higher |
| 283 |
guarantees are acceptable. |
| 284 |
The behavior of the available isolation levels is detailed in the |
| 285 |
following subsections. |
| 286 |
</para> |
| 287 |
|
| 288 |
<para> |
| 289 |
To set the transaction isolation level of a transaction, use the |
| 290 |
command <xref linkend="sql-set-transaction"/>. |
| 291 |
</para> |
| 292 |
|
| 293 |
<important> |
| 294 |
<para> |
| 295 |
Some <productname>PostgreSQL</productname> data types and functions have |
| 296 |
special rules regarding transactional behavior. In particular, changes |
| 297 |
made to a sequence (and therefore the counter of a |
| 298 |
column declared using <type>serial</type>) are immediately visible |
| 299 |
to all other transactions and are not rolled back if the transaction |
| 300 |
that made the changes aborts. See <xref linkend="functions-sequence"/> |
| 301 |
and <xref linkend="datatype-serial"/>. |
| 302 |
</para> |
| 303 |
</important> |
| 304 |
|
| 305 |
<sect2 id="xact-read-committed"> |
| 306 |
<title>Read Committed Isolation Level</title> |
| 307 |
|
| 308 |
<indexterm> |
| 309 |
<primary>transaction isolation level</primary> |
| 310 |
<secondary>read committed</secondary> |
| 311 |
</indexterm> |
| 312 |
|
| 313 |
<indexterm> |
| 314 |
<primary>read committed</primary> |
| 315 |
</indexterm> |
| 316 |
|
| 317 |
<para> |
| 318 |
<firstterm>Read Committed</firstterm> is the default isolation |
| 319 |
level in <productname>PostgreSQL</productname>. When a transaction |
| 320 |
uses this isolation level, a <command>SELECT</command> query |
| 321 |
(without a <literal>FOR UPDATE/SHARE</literal> clause) sees only data |
| 322 |
committed before the query began; it never sees either uncommitted |
| 323 |
data or changes committed by concurrent transactions during the query's |
| 324 |
execution. In effect, a <command>SELECT</command> query sees |
| 325 |
a snapshot of the database as of the instant the query begins to |
| 326 |
run. However, <command>SELECT</command> does see the effects |
| 327 |
of previous updates executed within its own transaction, even |
| 328 |
though they are not yet committed. Also note that two successive |
| 329 |
<command>SELECT</command> commands can see different data, even |
| 330 |
though they are within a single transaction, if other transactions |
| 331 |
commit changes after the first <command>SELECT</command> starts and |
| 332 |
before the second <command>SELECT</command> starts. |
| 333 |
</para> |
| 334 |
|
| 335 |
<para> |
| 336 |
<command>UPDATE</command>, <command>DELETE</command>, <command>SELECT |
| 337 |
FOR UPDATE</command>, and <command>SELECT FOR SHARE</command> commands |
| 338 |
behave the same as <command>SELECT</command> |
| 339 |
in terms of searching for target rows: they will only find target rows |
| 340 |
that were committed as of the command start time. However, such a target |
| 341 |
row might have already been updated (or deleted or locked) by |
| 342 |
another concurrent transaction by the time it is found. In this case, the |
| 343 |
would-be updater will wait for the first updating transaction to commit or |
| 344 |
roll back (if it is still in progress). If the first updater rolls back, |
| 345 |
then its effects are negated and the second updater can proceed with |
| 346 |
updating the originally found row. If the first updater commits, the |
| 347 |
second updater will ignore the row if the first updater deleted it, |
| 348 |
otherwise it will attempt to apply its operation to the updated version of |
| 349 |
the row. The search condition of the command (the <literal>WHERE</literal> clause) is |
| 350 |
re-evaluated to see if the updated version of the row still matches the |
| 351 |
search condition. If so, the second updater proceeds with its operation |
| 352 |
using the updated version of the row. In the case of |
| 353 |
<command>SELECT FOR UPDATE</command> and <command>SELECT FOR |
| 354 |
SHARE</command>, this means it is the updated version of the row that is |
| 355 |
locked and returned to the client. |
| 356 |
</para> |
| 357 |
|
| 358 |
<para> |
| 359 |
<command>INSERT</command> with an <literal>ON CONFLICT DO UPDATE</literal> clause |
| 360 |
behaves similarly. In Read Committed mode, each row proposed for insertion |
| 361 |
will either insert or update. Unless there are unrelated errors, one of |
| 362 |
those two outcomes is guaranteed. If a conflict originates in another |
| 363 |
transaction whose effects are not yet visible to the <command>INSERT</command>, |
| 364 |
the <command>UPDATE</command> clause will affect that row, |
| 365 |
even though possibly <emphasis>no</emphasis> version of that row is |
| 366 |
conventionally visible to the command. |
| 367 |
</para> |
| 368 |
|
| 369 |
<para> |
| 370 |
<command>INSERT</command> with an <literal>ON CONFLICT DO |
| 371 |
NOTHING</literal> clause may have insertion not proceed for a row due to |
| 372 |
the outcome of another transaction whose effects are not visible |
| 373 |
to the <command>INSERT</command> snapshot. Again, this is only |
| 374 |
the case in Read Committed mode. |
| 375 |
</para> |
| 376 |
|
| 377 |
<para> |
| 378 |
<command>MERGE</command> allows the user to specify various |
| 379 |
combinations of <command>INSERT</command>, <command>UPDATE</command> |
| 380 |
and <command>DELETE</command> subcommands. A <command>MERGE</command> |
| 381 |
command with both <command>INSERT</command> and <command>UPDATE</command> |
| 382 |
subcommands looks similar to <command>INSERT</command> with an |
| 383 |
<literal>ON CONFLICT DO UPDATE</literal> clause but does not |
| 384 |
guarantee that either <command>INSERT</command> or |
| 385 |
<command>UPDATE</command> will occur. |
| 386 |
If <command>MERGE</command> attempts an <command>UPDATE</command> or |
| 387 |
<command>DELETE</command> and the row is concurrently updated but |
| 388 |
the join condition still passes for the current target and the |
| 389 |
current source tuple, then <command>MERGE</command> will behave |
| 390 |
the same as the <command>UPDATE</command> or |
| 391 |
<command>DELETE</command> commands and perform its action on the |
| 392 |
updated version of the row. However, because <command>MERGE</command> |
| 393 |
can specify several actions and they can be conditional, the |
| 394 |
conditions for each action are re-evaluated on the updated version of |
| 395 |
the row, starting from the first action, even if the action that had |
| 396 |
originally matched appears later in the list of actions. |
| 397 |
On the other hand, if the row is concurrently updated so that the join |
| 398 |
condition fails, then <command>MERGE</command> will evaluate the |
| 399 |
command's <literal>NOT MATCHED BY SOURCE</literal> and |
| 400 |
<literal>NOT MATCHED [BY TARGET]</literal> actions next, and execute |
| 401 |
the first one of each kind that succeeds. |
| 402 |
If the row is concurrently deleted, then <command>MERGE</command> |
| 403 |
will evaluate the command's <literal>NOT MATCHED [BY TARGET]</literal> |
| 404 |
actions, and execute the first one that succeeds. |
| 405 |
If <command>MERGE</command> attempts an <command>INSERT</command> |
| 406 |
and a unique index is present and a duplicate row is concurrently |
| 407 |
inserted, then a uniqueness violation error is raised; |
| 408 |
<command>MERGE</command> does not attempt to avoid such |
| 409 |
errors by restarting evaluation of <literal>MATCHED</literal> |
| 410 |
conditions. |
| 411 |
</para> |
| 412 |
|
| 413 |
<para> |
| 414 |
Because of the above rules, it is possible for an updating command to see |
| 415 |
an inconsistent snapshot: it can see the effects of concurrent updating |
| 416 |
commands on the same rows it is trying to update, but it |
| 417 |
does not see effects of those commands on other rows in the database. |
| 418 |
This behavior makes Read Committed mode unsuitable for commands that |
| 419 |
involve complex search conditions; however, it is just right for simpler |
| 420 |
cases. For example, consider transferring $100 from one account |
| 421 |
to another: |
| 422 |
|
| 423 |
<screen> |
| 424 |
BEGIN; |
| 425 |
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; |
| 426 |
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534; |
| 427 |
COMMIT; |
| 428 |
</screen> |
| 429 |
|
| 430 |
If another transaction concurrently tries to change the balance of account |
| 431 |
7534, we clearly want the second statement to start with the updated |
| 432 |
version of the account's row. Because each command is affecting only a |
| 433 |
predetermined row, letting it see the updated version of the row does |
| 434 |
not create any troublesome inconsistency. |
| 435 |
</para> |
| 436 |
|
| 437 |
<para> |
| 438 |
More complex usage can produce undesirable results in Read Committed |
| 439 |
mode. For example, consider a <command>DELETE</command> command |
| 440 |
operating on data that is being both added and removed from its |
| 441 |
restriction criteria by another command, e.g., assume |
| 442 |
<literal>website</literal> is a two-row table with |
| 443 |
<literal>website.hits</literal> equaling <literal>9</literal> and |
| 444 |
<literal>10</literal>: |
| 445 |
|
| 446 |
<screen> |
| 447 |
BEGIN; |
| 448 |
UPDATE website SET hits = hits + 1; |
| 449 |
-- run from another session: DELETE FROM website WHERE hits = 10; |
| 450 |
COMMIT; |
| 451 |
</screen> |
| 452 |
|
| 453 |
The <command>DELETE</command> will have no effect even though |
| 454 |
there is a <literal>website.hits = 10</literal> row before and |
| 455 |
after the <command>UPDATE</command>. This occurs because the |
| 456 |
pre-update row value <literal>9</literal> is skipped, and when the |
| 457 |
<command>UPDATE</command> completes and <command>DELETE</command> |
| 458 |
obtains a lock, the new row value is no longer <literal>10</literal> but |
| 459 |
<literal>11</literal>, which no longer matches the criteria. |
| 460 |
</para> |
| 461 |
|
| 462 |
<para> |
| 463 |
Because Read Committed mode starts each command with a new snapshot |
| 464 |
that includes all transactions committed up to that instant, |
| 465 |
subsequent commands in the same transaction will see the effects |
| 466 |
of the committed concurrent transaction in any case. The point |
| 467 |
at issue above is whether or not a <emphasis>single</emphasis> command |
| 468 |
sees an absolutely consistent view of the database. |
| 469 |
</para> |
| 470 |
|
| 471 |
<para> |
| 472 |
The partial transaction isolation provided by Read Committed mode |
| 473 |
is adequate for many applications, and this mode is fast and simple |
| 474 |
to use; however, it is not sufficient for all cases. Applications |
| 475 |
that do complex queries and updates might require a more rigorously |
| 476 |
consistent view of the database than Read Committed mode provides. |
| 477 |
</para> |
| 478 |
</sect2> |
| 479 |
|
| 480 |
<sect2 id="xact-repeatable-read"> |
| 481 |
<title>Repeatable Read Isolation Level</title> |
| 482 |
|
| 483 |
<indexterm> |
| 484 |
<primary>transaction isolation level</primary> |
| 485 |
<secondary>repeatable read</secondary> |
| 486 |
</indexterm> |
| 487 |
|
| 488 |
<indexterm> |
| 489 |
<primary>repeatable read</primary> |
| 490 |
</indexterm> |
| 491 |
|
| 492 |
<para> |
| 493 |
The <firstterm>Repeatable Read</firstterm> isolation level only sees |
| 494 |
data committed before the transaction began; it never sees either |
| 495 |
uncommitted data or changes committed by concurrent transactions during |
| 496 |
the transaction's execution. (However, each query does see the |
| 497 |
effects of previous updates executed within its own transaction, |
| 498 |
even though they are not yet committed.) This is a stronger |
| 499 |
guarantee than is required by the <acronym>SQL</acronym> standard |
| 500 |
for this isolation level, and prevents all of the phenomena described |
| 501 |
in <xref linkend="mvcc-isolevel-table"/> except for serialization |
| 502 |
anomalies. As mentioned above, this is |
| 503 |
specifically allowed by the standard, which only describes the |
| 504 |
<emphasis>minimum</emphasis> protections each isolation level must |
| 505 |
provide. |
| 506 |
</para> |
| 507 |
|
| 508 |
<para> |
| 509 |
This level is different from Read Committed in that a query in a |
| 510 |
repeatable read transaction sees a snapshot as of the start of the |
| 511 |
first non-transaction-control statement in the |
| 512 |
<emphasis>transaction</emphasis>, not as of the start |
| 513 |
of the current statement within the transaction. Thus, successive |
| 514 |
<command>SELECT</command> commands within a <emphasis>single</emphasis> |
| 515 |
transaction see the same data, i.e., they do not see changes made by |
| 516 |
other transactions that committed after their own transaction started. |
| 517 |
</para> |
| 518 |
|
| 519 |
<para> |
| 520 |
Applications using this level must be prepared to retry transactions |
| 521 |
due to serialization failures. |
| 522 |
</para> |
| 523 |
|
| 524 |
<para> |
| 525 |
<command>UPDATE</command>, <command>DELETE</command>, |
| 526 |
<command>MERGE</command>, <command>SELECT FOR UPDATE</command>, |
| 527 |
and <command>SELECT FOR SHARE</command> commands |
| 528 |
behave the same as <command>SELECT</command> |
| 529 |
in terms of searching for target rows: they will only find target rows |
| 530 |
that were committed as of the transaction start time. However, such a |
| 531 |
target row might have already been updated (or deleted or locked) by |
| 532 |
another concurrent transaction by the time it is found. In this case, the |
| 533 |
repeatable read transaction will wait for the first updating transaction to commit or |
| 534 |
roll back (if it is still in progress). If the first updater rolls back, |
| 535 |
then its effects are negated and the repeatable read transaction can proceed |
| 536 |
with updating the originally found row. But if the first updater commits |
| 537 |
(and actually updated or deleted the row, not just locked it) |
| 538 |
then the repeatable read transaction will be rolled back with the message |
| 539 |
|
| 540 |
<screen> |
| 541 |
ERROR: could not serialize access due to concurrent update |
| 542 |
</screen> |
| 543 |
|
| 544 |
because a repeatable read transaction cannot modify or lock rows changed by |
| 545 |
other transactions after the repeatable read transaction began. |
| 546 |
</para> |
| 547 |
|
| 548 |
<para> |
| 549 |
When an application receives this error message, it should abort |
| 550 |
the current transaction and retry the whole transaction from |
| 551 |
the beginning. The second time through, the transaction will see the |
| 552 |
previously-committed change as part of its initial view of the database, |
| 553 |
so there is no logical conflict in using the new version of the row |
| 554 |
as the starting point for the new transaction's update. |
| 555 |
</para> |
| 556 |
|
| 557 |
<para> |
| 558 |
Note that only updating transactions might need to be retried; read-only |
| 559 |
transactions will never have serialization conflicts. |
| 560 |
</para> |
| 561 |
|
| 562 |
<para> |
| 563 |
The Repeatable Read mode provides a rigorous guarantee that each |
| 564 |
transaction sees a completely stable view of the database. However, |
| 565 |
this view will not necessarily always be consistent with some serial |
| 566 |
(one at a time) execution of concurrent transactions of the same level. |
| 567 |
For example, even a read-only transaction at this level may see a |
| 568 |
control record updated to show that a batch has been completed but |
| 569 |
<emphasis>not</emphasis> see one of the detail records which is logically |
| 570 |
part of the batch because it read an earlier revision of the control |
| 571 |
record. Attempts to enforce business rules by transactions running at |
| 572 |
this isolation level are not likely to work correctly without careful use |
| 573 |
of explicit locks to block conflicting transactions. |
| 574 |
</para> |
| 575 |
|
| 576 |
<para> |
| 577 |
The Repeatable Read isolation level is implemented using a technique |
| 578 |
known in academic database literature and in some other database products |
| 579 |
as <firstterm>Snapshot Isolation</firstterm>. Differences in behavior |
| 580 |
and performance may be observed when compared with systems that use a |
| 581 |
traditional locking technique that reduces concurrency. Some other |
| 582 |
systems may even offer Repeatable Read and Snapshot Isolation as distinct |
| 583 |
isolation levels with different behavior. The permitted phenomena that |
| 584 |
distinguish the two techniques were not formalized by database researchers |
| 585 |
until after the SQL standard was developed, and are outside the scope of |
| 586 |
this manual. For a full treatment, please see |
| 587 |
<xref linkend="berenson95"/>. |
| 588 |
</para> |
| 589 |
|
| 590 |
<note> |
| 591 |
<para> |
| 592 |
Prior to <productname>PostgreSQL</productname> version 9.1, a request |
| 593 |
for the Serializable transaction isolation level provided exactly the |
| 594 |
same behavior described here. To retain the legacy Serializable |
| 595 |
behavior, Repeatable Read should now be requested. |
| 596 |
</para> |
| 597 |
</note> |
| 598 |
</sect2> |
| 599 |
|
| 600 |
<sect2 id="xact-serializable"> |
| 601 |
<title>Serializable Isolation Level</title> |
| 602 |
|
| 603 |
<indexterm> |
| 604 |
<primary>transaction isolation level</primary> |
| 605 |
<secondary>serializable</secondary> |
| 606 |
</indexterm> |
| 607 |
|
| 608 |
<indexterm> |
| 609 |
<primary>serializable</primary> |
| 610 |
</indexterm> |
| 611 |
|
| 612 |
<indexterm> |
| 613 |
<primary>predicate locking</primary> |
| 614 |
</indexterm> |
| 615 |
|
| 616 |
<indexterm> |
| 617 |
<primary>serialization anomaly</primary> |
| 618 |
</indexterm> |
| 619 |
|
| 620 |
<para> |
| 621 |
The <firstterm>Serializable</firstterm> isolation level provides |
| 622 |
the strictest transaction isolation. This level emulates serial |
| 623 |
transaction execution for all committed transactions; |
| 624 |
as if transactions had been executed one after another, serially, |
| 625 |
rather than concurrently. However, like the Repeatable Read level, |
| 626 |
applications using this level must |
| 627 |
be prepared to retry transactions due to serialization failures. |
| 628 |
In fact, this isolation level works exactly the same as Repeatable |
| 629 |
Read except that it also monitors for conditions which could make |
| 630 |
execution of a concurrent set of serializable transactions behave |
| 631 |
in a manner inconsistent with all possible serial (one at a time) |
| 632 |
executions of those transactions. This monitoring does not |
| 633 |
introduce any blocking beyond that present in repeatable read, but |
| 634 |
there is some overhead to the monitoring, and detection of the |
| 635 |
conditions which could cause a |
| 636 |
<firstterm>serialization anomaly</firstterm> will trigger a |
| 637 |
<firstterm>serialization failure</firstterm>. |
| 638 |
</para> |
| 639 |
|
| 640 |
<para> |
| 641 |
As an example, |
| 642 |
consider a table <structname>mytab</structname>, initially containing: |
| 643 |
<screen> |
| 644 |
class | value |
| 645 |
-------+------- |
| 646 |
1 | 10 |
| 647 |
1 | 20 |
| 648 |
2 | 100 |
| 649 |
2 | 200 |
| 650 |
</screen> |
| 651 |
Suppose that serializable transaction A computes: |
| 652 |
<screen> |
| 653 |
SELECT SUM(value) FROM mytab WHERE class = 1; |
| 654 |
</screen> |
| 655 |
and then inserts the result (30) as the <structfield>value</structfield> in a |
| 656 |
new row with <structfield>class</structfield><literal> = 2</literal>. Concurrently, serializable |
| 657 |
transaction B computes: |
| 658 |
<screen> |
| 659 |
SELECT SUM(value) FROM mytab WHERE class = 2; |
| 660 |
</screen> |
| 661 |
and obtains the result 300, which it inserts in a new row with |
| 662 |
<structfield>class</structfield><literal> = 1</literal>. Then both transactions try to commit. |
| 663 |
If either transaction were running at the Repeatable Read isolation level, |
| 664 |
both would be allowed to commit; but since there is no serial order of execution |
| 665 |
consistent with the result, using Serializable transactions will allow one |
| 666 |
transaction to commit and will roll the other back with this message: |
| 667 |
|
| 668 |
<screen> |
| 669 |
ERROR: could not serialize access due to read/write dependencies among transactions |
| 670 |
</screen> |
| 671 |
|
| 672 |
This is because if A had |
| 673 |
executed before B, B would have computed the sum 330, not 300, and |
| 674 |
similarly the other order would have resulted in a different sum |
| 675 |
computed by A. |
| 676 |
</para> |
| 677 |
|
| 678 |
<para> |
| 679 |
When relying on Serializable transactions to prevent anomalies, it is |
| 680 |
important that any data read from a permanent user table not be |
| 681 |
considered valid until the transaction which read it has successfully |
| 682 |
committed. This is true even for read-only transactions, except that |
| 683 |
data read within a <firstterm>deferrable</firstterm> read-only |
| 684 |
transaction is known to be valid as soon as it is read, because such a |
| 685 |
transaction waits until it can acquire a snapshot guaranteed to be free |
| 686 |
from such problems before starting to read any data. In all other cases |
| 687 |
applications must not depend on results read during a transaction that |
| 688 |
later aborted; instead, they should retry the transaction until it |
| 689 |
succeeds. |
| 690 |
</para> |
| 691 |
|
| 692 |
<para> |
| 693 |
To guarantee true serializability <productname>PostgreSQL</productname> |
| 694 |
uses <firstterm>predicate locking</firstterm>, which means that it keeps locks |
| 695 |
which allow it to determine when a write would have had an impact on |
| 696 |
the result of a previous read from a concurrent transaction, had it run |
| 697 |
first. In <productname>PostgreSQL</productname> these locks do not |
| 698 |
cause any blocking and therefore can <emphasis>not</emphasis> play any part in |
| 699 |
causing a deadlock. They are used to identify and flag dependencies |
| 700 |
among concurrent Serializable transactions which in certain combinations |
| 701 |
can lead to serialization anomalies. In contrast, a Read Committed or |
| 702 |
Repeatable Read transaction which wants to ensure data consistency may |
| 703 |
need to take out a lock on an entire table, which could block other |
| 704 |
users attempting to use that table, or it may use <literal>SELECT FOR |
| 705 |
UPDATE</literal> or <literal>SELECT FOR SHARE</literal> which not only |
| 706 |
can block other transactions but cause disk access. |
| 707 |
</para> |
| 708 |
|
| 709 |
<para> |
| 710 |
Predicate locks in <productname>PostgreSQL</productname>, like in most |
| 711 |
other database systems, are based on data actually accessed by a |
| 712 |
transaction. These will show up in the |
| 713 |
<link linkend="view-pg-locks"><structname>pg_locks</structname></link> |
| 714 |
system view with a <literal>mode</literal> of <literal>SIReadLock</literal>. The |
| 715 |
particular locks |
| 716 |
acquired during execution of a query will depend on the plan used by |
| 717 |
the query, and multiple finer-grained locks (e.g., tuple locks) may be |
| 718 |
combined into fewer coarser-grained locks (e.g., page locks) during the |
| 719 |
course of the transaction to prevent exhaustion of the memory used to |
| 720 |
track the locks. A <literal>READ ONLY</literal> transaction may be able to |
| 721 |
release its SIRead locks before completion, if it detects that no |
| 722 |
conflicts can still occur which could lead to a serialization anomaly. |
| 723 |
In fact, <literal>READ ONLY</literal> transactions will often be able to |
| 724 |
establish that fact at startup and avoid taking any predicate locks. |
| 725 |
If you explicitly request a <literal>SERIALIZABLE READ ONLY DEFERRABLE</literal> |
| 726 |
transaction, it will block until it can establish this fact. (This is |
| 727 |
the <emphasis>only</emphasis> case where Serializable transactions block but |
| 728 |
Repeatable Read transactions don't.) On the other hand, SIRead locks |
| 729 |
often need to be kept past transaction commit, until overlapping read |
| 730 |
write transactions complete. |
| 731 |
</para> |
| 732 |
|
| 733 |
<para> |
| 734 |
Consistent use of Serializable transactions can simplify development. |
| 735 |
The guarantee that any set of successfully committed concurrent |
| 736 |
Serializable transactions will have the same effect as if they were run |
| 737 |
one at a time means that if you can demonstrate that a single transaction, |
| 738 |
as written, will do the right thing when run by itself, you can have |
| 739 |
confidence that it will do the right thing in any mix of Serializable |
| 740 |
transactions, even without any information about what those other |
| 741 |
transactions might do, or it will not successfully commit. It is |
| 742 |
important that an environment which uses this technique have a |
| 743 |
generalized way of handling serialization failures (which always return |
| 744 |
with an SQLSTATE value of '40001'), because it will be very hard to |
| 745 |
predict exactly which transactions might contribute to the read/write |
| 746 |
dependencies and need to be rolled back to prevent serialization |
| 747 |
anomalies. The monitoring of read/write dependencies has a cost, as does |
| 748 |
the restart of transactions which are terminated with a serialization |
| 749 |
failure, but balanced against the cost and blocking involved in use of |
| 750 |
explicit locks and <literal>SELECT FOR UPDATE</literal> or <literal>SELECT FOR |
| 751 |
SHARE</literal>, Serializable transactions are the best performance choice |
| 752 |
for some environments. |
| 753 |
</para> |
| 754 |
|
| 755 |
<para> |
| 756 |
While <productname>PostgreSQL</productname>'s Serializable transaction isolation |
| 757 |
level only allows concurrent transactions to commit if it can prove there |
| 758 |
is a serial order of execution that would produce the same effect, it |
| 759 |
doesn't always prevent errors from being raised that would not occur in |
| 760 |
true serial execution. In particular, it is possible to see unique |
| 761 |
constraint violations caused by conflicts with overlapping Serializable |
| 762 |
transactions even after explicitly checking that the key isn't present |
| 763 |
before attempting to insert it. This can be avoided by making sure |
| 764 |
that <emphasis>all</emphasis> Serializable transactions that insert potentially |
| 765 |
conflicting keys explicitly check if they can do so first. For example, |
| 766 |
imagine an application that asks the user for a new key and then checks |
| 767 |
that it doesn't exist already by trying to select it first, or generates |
| 768 |
a new key by selecting the maximum existing key and adding one. If some |
| 769 |
Serializable transactions insert new keys directly without following this |
| 770 |
protocol, unique constraints violations might be reported even in cases |
| 771 |
where they could not occur in a serial execution of the concurrent |
| 772 |
transactions. |
| 773 |
</para> |
| 774 |
|
| 775 |
<para> |
| 776 |
For optimal performance when relying on Serializable transactions for |
| 777 |
concurrency control, these issues should be considered: |
| 778 |
|
| 779 |
<itemizedlist> |
| 780 |
<listitem> |
| 781 |
<para> |
| 782 |
Declare transactions as <literal>READ ONLY</literal> when possible. |
| 783 |
</para> |
| 784 |
</listitem> |
| 785 |
<listitem> |
| 786 |
<para> |
| 787 |
Control the number of active connections, using a connection pool if |
| 788 |
needed. This is always an important performance consideration, but |
| 789 |
it can be particularly important in a busy system using Serializable |
| 790 |
transactions. |
| 791 |
</para> |
| 792 |
</listitem> |
| 793 |
<listitem> |
| 794 |
<para> |
| 795 |
Don't put more into a single transaction than needed for integrity |
| 796 |
purposes. |
| 797 |
</para> |
| 798 |
</listitem> |
| 799 |
<listitem> |
| 800 |
<para> |
| 801 |
Don't leave connections dangling <quote>idle in transaction</quote> |
| 802 |
longer than necessary. The configuration parameter |
| 803 |
<xref linkend="guc-idle-in-transaction-session-timeout"/> may be used to |
| 804 |
automatically disconnect lingering sessions. |
| 805 |
</para> |
| 806 |
</listitem> |
| 807 |
<listitem> |
| 808 |
<para> |
| 809 |
Eliminate explicit locks, <literal>SELECT FOR UPDATE</literal>, and |
| 810 |
<literal>SELECT FOR SHARE</literal> where no longer needed due to the |
| 811 |
protections automatically provided by Serializable transactions. |
| 812 |
</para> |
| 813 |
</listitem> |
| 814 |
<listitem> |
| 815 |
<para> |
| 816 |
When the system is forced to combine multiple page-level predicate |
| 817 |
locks into a single relation-level predicate lock because the predicate |
| 818 |
lock table is short of memory, an increase in the rate of serialization |
| 819 |
failures may occur. You can avoid this by increasing |
| 820 |
<xref linkend="guc-max-pred-locks-per-transaction"/>, |
| 821 |
<xref linkend="guc-max-pred-locks-per-relation"/>, and/or |
| 822 |
<xref linkend="guc-max-pred-locks-per-page"/>. |
| 823 |
</para> |
| 824 |
</listitem> |
| 825 |
<listitem> |
| 826 |
<para> |
| 827 |
A sequential scan will always necessitate a relation-level predicate |
| 828 |
lock. This can result in an increased rate of serialization failures. |
| 829 |
It may be helpful to encourage the use of index scans by reducing |
| 830 |
<xref linkend="guc-random-page-cost"/> and/or increasing |
| 831 |
<xref linkend="guc-cpu-tuple-cost"/>. Be sure to weigh any decrease |
| 832 |
in transaction rollbacks and restarts against any overall change in |
| 833 |
query execution time. |
| 834 |
</para> |
| 835 |
</listitem> |
| 836 |
</itemizedlist> |
| 837 |
</para> |
| 838 |
|
| 839 |
<para> |
| 840 |
The Serializable isolation level is implemented using a technique known |
| 841 |
in academic database literature as Serializable Snapshot Isolation, which |
| 842 |
builds on Snapshot Isolation by adding checks for serialization anomalies. |
| 843 |
Some differences in behavior and performance may be observed when compared |
| 844 |
with other systems that use a traditional locking technique. Please see |
| 845 |
<xref linkend="ports12"/> for detailed information. |
| 846 |
</para> |
| 847 |
</sect2> |
| 848 |
</sect1> |
| 849 |
|
| 850 |
<sect1 id="explicit-locking"> |
| 851 |
<title>Explicit Locking</title> |
| 852 |
|
| 853 |
<indexterm> |
| 854 |
<primary>lock</primary> |
| 855 |
</indexterm> |
| 856 |
|
| 857 |
<para> |
| 858 |
<productname>PostgreSQL</productname> provides various lock modes |
| 859 |
to control concurrent access to data in tables. These modes can |
| 860 |
be used for application-controlled locking in situations where |
| 861 |
<acronym>MVCC</acronym> does not give the desired behavior. Also, |
| 862 |
most <productname>PostgreSQL</productname> commands automatically |
| 863 |
acquire locks of appropriate modes to ensure that referenced |
| 864 |
tables are not dropped or modified in incompatible ways while the |
| 865 |
command executes. (For example, <command>TRUNCATE</command> cannot safely be |
| 866 |
executed concurrently with other operations on the same table, so it |
| 867 |
obtains an <literal>ACCESS EXCLUSIVE</literal> lock on the table to |
| 868 |
enforce that.) |
| 869 |
</para> |
| 870 |
|
| 871 |
<para> |
| 872 |
To examine a list of the currently outstanding locks in a database |
| 873 |
server, use the |
| 874 |
<link linkend="view-pg-locks"><structname>pg_locks</structname></link> |
| 875 |
system view. For more information on monitoring the status of the lock |
| 876 |
manager subsystem, refer to <xref linkend="monitoring"/>. |
| 877 |
</para> |
| 878 |
|
| 879 |
<sect2 id="locking-tables"> |
| 880 |
<title>Table-Level Locks</title> |
| 881 |
|
| 882 |
<indexterm zone="locking-tables"> |
| 883 |
<primary>LOCK</primary> |
| 884 |
</indexterm> |
| 885 |
|
| 886 |
<para> |
| 887 |
The list below shows the available lock modes and the contexts in |
| 888 |
which they are used automatically by |
| 889 |
<productname>PostgreSQL</productname>. You can also acquire any |
| 890 |
of these locks explicitly with the command <xref |
| 891 |
linkend="sql-lock"/>. |
| 892 |
Remember that all of these lock modes are table-level locks, |
| 893 |
even if the name contains the word |
| 894 |
<quote>row</quote>; the names of the lock modes are historical. |
| 895 |
To some extent the names reflect the typical usage of each lock |
| 896 |
mode — but the semantics are all the same. The only real difference |
| 897 |
between one lock mode and another is the set of lock modes with |
| 898 |
which each conflicts (see <xref linkend="table-lock-compatibility"/>). |
| 899 |
Two transactions cannot hold locks of conflicting |
| 900 |
modes on the same table at the same time. (However, a transaction |
| 901 |
never conflicts with itself. For example, it might acquire |
| 902 |
<literal>ACCESS EXCLUSIVE</literal> lock and later acquire |
| 903 |
<literal>ACCESS SHARE</literal> lock on the same table.) Non-conflicting |
| 904 |
lock modes can be held concurrently by many transactions. Notice in |
| 905 |
particular that some lock modes are self-conflicting (for example, |
| 906 |
an <literal>ACCESS EXCLUSIVE</literal> lock cannot be held by more than one |
| 907 |
transaction at a time) while others are not self-conflicting (for example, |
| 908 |
an <literal>ACCESS SHARE</literal> lock can be held by multiple transactions). |
| 909 |
</para> |
| 910 |
|
| 911 |
<variablelist> |
| 912 |
<title>Table-Level Lock Modes</title> |
| 913 |
<varlistentry> |
| 914 |
<term> |
| 915 |
<literal>ACCESS SHARE</literal> (<literal>AccessShareLock</literal>) |
| 916 |
</term> |
| 917 |
<listitem> |
| 918 |
<para> |
| 919 |
Conflicts with the <literal>ACCESS EXCLUSIVE</literal> lock |
| 920 |
mode only. |
| 921 |
</para> |
| 922 |
|
| 923 |
<para> |
| 924 |
The <command>SELECT</command> command acquires a lock of this mode on |
| 925 |
referenced tables. In general, any query that only <emphasis>reads</emphasis> a table |
| 926 |
and does not modify it will acquire this lock mode. |
| 927 |
</para> |
| 928 |
</listitem> |
| 929 |
</varlistentry> |
| 930 |
|
| 931 |
<varlistentry> |
| 932 |
<term> |
| 933 |
<literal>ROW SHARE</literal> (<literal>RowShareLock</literal>) |
| 934 |
</term> |
| 935 |
<listitem> |
| 936 |
<para> |
| 937 |
Conflicts with the <literal>EXCLUSIVE</literal> and |
| 938 |
<literal>ACCESS EXCLUSIVE</literal> lock modes. |
| 939 |
</para> |
| 940 |
|
| 941 |
<para> |
| 942 |
The <command>SELECT</command> command acquires a lock of this mode |
| 943 |
on all tables on which one of the <option>FOR UPDATE</option>, |
| 944 |
<option>FOR NO KEY UPDATE</option>, |
| 945 |
<option>FOR SHARE</option>, or |
| 946 |
<option>FOR KEY SHARE</option> options is specified |
| 947 |
(in addition to <literal>ACCESS SHARE</literal> locks on any other |
| 948 |
tables that are referenced without any explicit |
| 949 |
<option>FOR ...</option> locking option). |
| 950 |
</para> |
| 951 |
</listitem> |
| 952 |
</varlistentry> |
| 953 |
|
| 954 |
<varlistentry> |
| 955 |
<term> |
| 956 |
<literal>ROW EXCLUSIVE</literal> (<literal>RowExclusiveLock</literal>) |
| 957 |
</term> |
| 958 |
<listitem> |
| 959 |
<para> |
| 960 |
Conflicts with the <literal>SHARE</literal>, <literal>SHARE ROW |
| 961 |
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and |
| 962 |
<literal>ACCESS EXCLUSIVE</literal> lock modes. |
| 963 |
</para> |
| 964 |
|
| 965 |
<para> |
| 966 |
The commands <command>UPDATE</command>, |
| 967 |
<command>DELETE</command>, <command>INSERT</command>, and |
| 968 |
<command>MERGE</command> |
| 969 |
acquire this lock mode on the target table (in addition to |
| 970 |
<literal>ACCESS SHARE</literal> locks on any other referenced |
| 971 |
tables). In general, this lock mode will be acquired by any |
| 972 |
command that <emphasis>modifies data</emphasis> in a table. |
| 973 |
</para> |
| 974 |
</listitem> |
| 975 |
</varlistentry> |
| 976 |
|
| 977 |
<varlistentry> |
| 978 |
<term> |
| 979 |
<literal>SHARE UPDATE EXCLUSIVE</literal> (<literal>ShareUpdateExclusiveLock</literal>) |
| 980 |
</term> |
| 981 |
<listitem> |
| 982 |
<para> |
| 983 |
Conflicts with the <literal>SHARE UPDATE EXCLUSIVE</literal>, |
| 984 |
<literal>SHARE</literal>, <literal>SHARE ROW |
| 985 |
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and |
| 986 |
<literal>ACCESS EXCLUSIVE</literal> lock modes. |
| 987 |
This mode protects a table against |
| 988 |
concurrent schema changes and <command>VACUUM</command> runs. |
| 989 |
</para> |
| 990 |
|
| 991 |
<para> |
| 992 |
Acquired by <command>VACUUM</command> (without <option>FULL</option>), |
| 993 |
<command>ANALYZE</command>, <command>CREATE INDEX CONCURRENTLY</command>, |
| 994 |
<command>CREATE STATISTICS</command>, <command>COMMENT ON</command>, |
| 995 |
<command>REINDEX CONCURRENTLY</command>, |
| 996 |
and certain <link linkend="sql-alterindex"><command>ALTER INDEX</command></link> |
| 997 |
and <link linkend="sql-altertable"><command>ALTER TABLE</command></link> variants |
| 998 |
(for full details see the documentation of these commands). |
| 999 |
</para> |
| 1000 |
</listitem> |
| 1001 |
</varlistentry> |
| 1002 |
|
| 1003 |
<varlistentry> |
| 1004 |
<term> |
| 1005 |
<literal>SHARE</literal> (<literal>ShareLock</literal>) |
| 1006 |
</term> |
| 1007 |
<listitem> |
| 1008 |
<para> |
| 1009 |
Conflicts with the <literal>ROW EXCLUSIVE</literal>, |
| 1010 |
<literal>SHARE UPDATE EXCLUSIVE</literal>, <literal>SHARE ROW |
| 1011 |
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and |
| 1012 |
<literal>ACCESS EXCLUSIVE</literal> lock modes. |
| 1013 |
This mode protects a table against concurrent data changes. |
| 1014 |
</para> |
| 1015 |
|
| 1016 |
<para> |
| 1017 |
Acquired by <command>CREATE INDEX</command> |
| 1018 |
(without <option>CONCURRENTLY</option>). |
| 1019 |
</para> |
| 1020 |
</listitem> |
| 1021 |
</varlistentry> |
| 1022 |
|
| 1023 |
<varlistentry> |
| 1024 |
<term> |
| 1025 |
<literal>SHARE ROW EXCLUSIVE</literal> (<literal>ShareRowExclusiveLock</literal>) |
| 1026 |
</term> |
| 1027 |
<listitem> |
| 1028 |
<para> |
| 1029 |
Conflicts with the <literal>ROW EXCLUSIVE</literal>, |
| 1030 |
<literal>SHARE UPDATE EXCLUSIVE</literal>, |
| 1031 |
<literal>SHARE</literal>, <literal>SHARE ROW |
| 1032 |
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and |
| 1033 |
<literal>ACCESS EXCLUSIVE</literal> lock modes. |
| 1034 |
This mode protects a table against concurrent data changes, and |
| 1035 |
is self-exclusive so that only one session can hold it at a time. |
| 1036 |
</para> |
| 1037 |
|
| 1038 |
<para> |
| 1039 |
Acquired by <command>CREATE TRIGGER</command> and some forms of |
| 1040 |
<link linkend="sql-altertable"><command>ALTER TABLE</command></link>. |
| 1041 |
</para> |
| 1042 |
</listitem> |
| 1043 |
</varlistentry> |
| 1044 |
|
| 1045 |
<varlistentry> |
| 1046 |
<term> |
| 1047 |
<literal>EXCLUSIVE</literal> (<literal>ExclusiveLock</literal>) |
| 1048 |
</term> |
| 1049 |
<listitem> |
| 1050 |
<para> |
| 1051 |
Conflicts with the <literal>ROW SHARE</literal>, <literal>ROW |
| 1052 |
EXCLUSIVE</literal>, <literal>SHARE UPDATE |
| 1053 |
EXCLUSIVE</literal>, <literal>SHARE</literal>, <literal>SHARE |
| 1054 |
ROW EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and |
| 1055 |
<literal>ACCESS EXCLUSIVE</literal> lock modes. |
| 1056 |
This mode allows only concurrent <literal>ACCESS SHARE</literal> locks, |
| 1057 |
i.e., only reads from the table can proceed in parallel with a |
| 1058 |
transaction holding this lock mode. |
| 1059 |
</para> |
| 1060 |
|
| 1061 |
<para> |
| 1062 |
Acquired by <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command>. |
| 1063 |
</para> |
| 1064 |
</listitem> |
| 1065 |
</varlistentry> |
| 1066 |
|
| 1067 |
<varlistentry> |
| 1068 |
<term> |
| 1069 |
<literal>ACCESS EXCLUSIVE</literal> (<literal>AccessExclusiveLock</literal>) |
| 1070 |
</term> |
| 1071 |
<listitem> |
| 1072 |
<para> |
| 1073 |
Conflicts with locks of all modes (<literal>ACCESS |
| 1074 |
SHARE</literal>, <literal>ROW SHARE</literal>, <literal>ROW |
| 1075 |
EXCLUSIVE</literal>, <literal>SHARE UPDATE |
| 1076 |
EXCLUSIVE</literal>, <literal>SHARE</literal>, <literal>SHARE |
| 1077 |
ROW EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and |
| 1078 |
<literal>ACCESS EXCLUSIVE</literal>). |
| 1079 |
This mode guarantees that the |
| 1080 |
holder is the only transaction accessing the table in any way. |
| 1081 |
</para> |
| 1082 |
|
| 1083 |
<para> |
| 1084 |
Acquired by the <command>DROP TABLE</command>, |
| 1085 |
<command>TRUNCATE</command>, <command>REINDEX</command>, |
| 1086 |
<command>CLUSTER</command>, <command>VACUUM FULL</command>, |
| 1087 |
and <command>REFRESH MATERIALIZED VIEW</command> (without |
| 1088 |
<option>CONCURRENTLY</option>) |
| 1089 |
commands. Many forms of <command>ALTER INDEX</command> and <command>ALTER TABLE</command> also acquire |
| 1090 |
a lock at this level. This is also the default lock mode for |
| 1091 |
<command>LOCK TABLE</command> statements that do not specify |
| 1092 |
a mode explicitly. |
| 1093 |
</para> |
| 1094 |
</listitem> |
| 1095 |
</varlistentry> |
| 1096 |
</variablelist> |
| 1097 |
|
| 1098 |
<tip> |
| 1099 |
<para> |
| 1100 |
Only an <literal>ACCESS EXCLUSIVE</literal> lock blocks a |
| 1101 |
<command>SELECT</command> (without <option>FOR UPDATE/SHARE</option>) |
| 1102 |
statement. |
| 1103 |
</para> |
| 1104 |
</tip> |
| 1105 |
|
| 1106 |
<para> |
| 1107 |
Once acquired, a lock is normally held until the end of the transaction. But if a |
| 1108 |
lock is acquired after establishing a savepoint, the lock is released |
| 1109 |
immediately if the savepoint is rolled back to. This is consistent with |
| 1110 |
the principle that <command>ROLLBACK</command> cancels all effects of the |
| 1111 |
commands since the savepoint. The same holds for locks acquired within a |
| 1112 |
<application>PL/pgSQL</application> exception block: an error escape from the block |
| 1113 |
releases locks acquired within it. |
| 1114 |
</para> |
| 1115 |
|
| 1116 |
|
| 1117 |
|
| 1118 |
<table tocentry="1" id="table-lock-compatibility"> |
| 1119 |
<title>Conflicting Lock Modes</title> |
| 1120 |
<tgroup cols="9"> |
| 1121 |
<colspec colnum="1" colwidth="1.25*"/> |
| 1122 |
<colspec colnum="2" colwidth="1*" colname="lockst"/> |
| 1123 |
<colspec colnum="3" colwidth="1*"/> |
| 1124 |
<colspec colnum="4" colwidth="1*"/> |
| 1125 |
<colspec colnum="5" colwidth="1*"/> |
| 1126 |
<colspec colnum="6" colwidth="1*"/> |
| 1127 |
<colspec colnum="7" colwidth="1*"/> |
| 1128 |
<colspec colnum="8" colwidth="1*"/> |
| 1129 |
<colspec colnum="9" colwidth="1*" colname="lockend"/> |
| 1130 |
<spanspec spanname="lockreq" namest="lockst" nameend="lockend" align="center"/> |
| 1131 |
<thead> |
| 1132 |
<row> |
| 1133 |
<entry morerows="1">Requested Lock Mode</entry> |
| 1134 |
<entry spanname="lockreq">Existing Lock Mode</entry> |
| 1135 |
</row> |
| 1136 |
<row> |
| 1137 |
<entry><literal>ACCESS SHARE</literal></entry> |
| 1138 |
<entry><literal>ROW SHARE</literal></entry> |
| 1139 |
<entry><literal>ROW EXCL.</literal></entry> |
| 1140 |
<entry><literal>SHARE UPDATE EXCL.</literal></entry> |
| 1141 |
<entry><literal>SHARE</literal></entry> |
| 1142 |
<entry><literal>SHARE ROW EXCL.</literal></entry> |
| 1143 |
<entry><literal>EXCL.</literal></entry> |
| 1144 |
<entry><literal>ACCESS EXCL.</literal></entry> |
| 1145 |
</row> |
| 1146 |
</thead> |
| 1147 |
<tbody> |
| 1148 |
<row> |
| 1149 |
<entry><literal>ACCESS SHARE</literal></entry> |
| 1150 |
<entry align="center"></entry> |
| 1151 |
<entry align="center"></entry> |
| 1152 |
<entry align="center"></entry> |
| 1153 |
<entry align="center"></entry> |
| 1154 |
<entry align="center"></entry> |
| 1155 |
<entry align="center"></entry> |
| 1156 |
<entry align="center"></entry> |
| 1157 |
<entry align="center">X</entry> |
| 1158 |
</row> |
| 1159 |
<row> |
| 1160 |
<entry><literal>ROW SHARE</literal></entry> |
| 1161 |
<entry align="center"></entry> |
| 1162 |
<entry align="center"></entry> |
| 1163 |
<entry align="center"></entry> |
| 1164 |
<entry align="center"></entry> |
| 1165 |
<entry align="center"></entry> |
| 1166 |
<entry align="center"></entry> |
| 1167 |
<entry align="center">X</entry> |
| 1168 |
<entry align="center">X</entry> |
| 1169 |
</row> |
| 1170 |
<row> |
| 1171 |
<entry><literal>ROW EXCL.</literal></entry> |
| 1172 |
<entry align="center"></entry> |
| 1173 |
<entry align="center"></entry> |
| 1174 |
<entry align="center"></entry> |
| 1175 |
<entry align="center"></entry> |
| 1176 |
<entry align="center">X</entry> |
| 1177 |
<entry align="center">X</entry> |
| 1178 |
<entry align="center">X</entry> |
| 1179 |
<entry align="center">X</entry> |
| 1180 |
</row> |
| 1181 |
<row> |
| 1182 |
<entry><literal>SHARE UPDATE EXCL.</literal></entry> |
| 1183 |
<entry align="center"></entry> |
| 1184 |
<entry align="center"></entry> |
| 1185 |
<entry align="center"></entry> |
| 1186 |
<entry align="center">X</entry> |
| 1187 |
<entry align="center">X</entry> |
| 1188 |
<entry align="center">X</entry> |
| 1189 |
<entry align="center">X</entry> |
| 1190 |
<entry align="center">X</entry> |
| 1191 |
</row> |
| 1192 |
<row> |
| 1193 |
<entry><literal>SHARE</literal></entry> |
| 1194 |
<entry align="center"></entry> |
| 1195 |
<entry align="center"></entry> |
| 1196 |
<entry align="center">X</entry> |
| 1197 |
<entry align="center">X</entry> |
| 1198 |
<entry align="center"></entry> |
| 1199 |
<entry align="center">X</entry> |
| 1200 |
<entry align="center">X</entry> |
| 1201 |
<entry align="center">X</entry> |
| 1202 |
</row> |
| 1203 |
<row> |
| 1204 |
<entry><literal>SHARE ROW EXCL.</literal></entry> |
| 1205 |
<entry align="center"></entry> |
| 1206 |
<entry align="center"></entry> |
| 1207 |
<entry align="center">X</entry> |
| 1208 |
<entry align="center">X</entry> |
| 1209 |
<entry align="center">X</entry> |
| 1210 |
<entry align="center">X</entry> |
| 1211 |
<entry align="center">X</entry> |
| 1212 |
<entry align="center">X</entry> |
| 1213 |
</row> |
| 1214 |
<row> |
| 1215 |
<entry><literal>EXCL.</literal></entry> |
| 1216 |
<entry align="center"></entry> |
| 1217 |
<entry align="center">X</entry> |
| 1218 |
<entry align="center">X</entry> |
| 1219 |
<entry align="center">X</entry> |
| 1220 |
<entry align="center">X</entry> |
| 1221 |
<entry align="center">X</entry> |
| 1222 |
<entry align="center">X</entry> |
| 1223 |
<entry align="center">X</entry> |
| 1224 |
</row> |
| 1225 |
<row> |
| 1226 |
<entry><literal>ACCESS EXCL.</literal></entry> |
| 1227 |
<entry align="center">X</entry> |
| 1228 |
<entry align="center">X</entry> |
| 1229 |
<entry align="center">X</entry> |
| 1230 |
<entry align="center">X</entry> |
| 1231 |
<entry align="center">X</entry> |
| 1232 |
<entry align="center">X</entry> |
| 1233 |
<entry align="center">X</entry> |
| 1234 |
<entry align="center">X</entry> |
| 1235 |
</row> |
| 1236 |
</tbody> |
| 1237 |
</tgroup> |
| 1238 |
</table> |
| 1239 |
</sect2> |
| 1240 |
|
| 1241 |
<sect2 id="locking-rows"> |
| 1242 |
<title>Row-Level Locks</title> |
| 1243 |
|
| 1244 |
<para> |
| 1245 |
In addition to table-level locks, there are row-level locks, which |
| 1246 |
are listed as below with the contexts in which they are used |
| 1247 |
automatically by <productname>PostgreSQL</productname>. See |
| 1248 |
<xref linkend="row-lock-compatibility"/> for a complete table of |
| 1249 |
row-level lock conflicts. Note that a transaction can hold |
| 1250 |
conflicting locks on the same row, even in different subtransactions; |
| 1251 |
but other than that, two transactions can never hold conflicting locks |
| 1252 |
on the same row. Row-level locks do not affect data querying; they |
| 1253 |
block only <emphasis>writers and lockers</emphasis> to the same |
| 1254 |
row. Row-level locks are released at transaction end or during |
| 1255 |
savepoint rollback, just like table-level locks. |
| 1256 |
|
| 1257 |
</para> |
| 1258 |
|
| 1259 |
<variablelist> |
| 1260 |
<title>Row-Level Lock Modes</title> |
| 1261 |
<varlistentry> |
| 1262 |
<term> |
| 1263 |
<literal>FOR UPDATE</literal> |
| 1264 |
</term> |
| 1265 |
<listitem> |
| 1266 |
<para> |
| 1267 |
<literal>FOR UPDATE</literal> causes the rows retrieved by the |
| 1268 |
<command>SELECT</command> statement to be locked as though for |
| 1269 |
update. This prevents them from being locked, modified or deleted by |
| 1270 |
other transactions until the current transaction ends. That is, |
| 1271 |
other transactions that attempt <command>UPDATE</command>, |
| 1272 |
<command>DELETE</command>, |
| 1273 |
<command>SELECT FOR UPDATE</command>, |
| 1274 |
<command>SELECT FOR NO KEY UPDATE</command>, |
| 1275 |
<command>SELECT FOR SHARE</command> or |
| 1276 |
<command>SELECT FOR KEY SHARE</command> |
| 1277 |
of these rows will be blocked until the current transaction ends; |
| 1278 |
conversely, <command>SELECT FOR UPDATE</command> will wait for a |
| 1279 |
concurrent transaction that has run any of those commands on the |
| 1280 |
same row, |
| 1281 |
and will then lock and return the updated row (or no row, if the |
| 1282 |
row was deleted). Within a <literal>REPEATABLE READ</literal> or |
| 1283 |
<literal>SERIALIZABLE</literal> transaction, |
| 1284 |
however, an error will be thrown if a row to be locked has changed |
| 1285 |
since the transaction started. For further discussion see |
| 1286 |
<xref linkend="applevel-consistency"/>. |
| 1287 |
</para> |
| 1288 |
<para> |
| 1289 |
The <literal>FOR UPDATE</literal> lock mode |
| 1290 |
is also acquired by any <command>DELETE</command> on a row, and also by an |
| 1291 |
<command>UPDATE</command> that modifies the values of certain columns. Currently, |
| 1292 |
the set of columns considered for the <command>UPDATE</command> case are those that |
| 1293 |
have a unique index on them that can be used in a foreign key (so partial |
| 1294 |
indexes and expressional indexes are not considered), but this may change |
| 1295 |
in the future. |
| 1296 |
</para> |
| 1297 |
</listitem> |
| 1298 |
</varlistentry> |
| 1299 |
|
| 1300 |
<varlistentry> |
| 1301 |
<term> |
| 1302 |
<literal>FOR NO KEY UPDATE</literal> |
| 1303 |
</term> |
| 1304 |
<listitem> |
| 1305 |
<para> |
| 1306 |
Behaves similarly to <literal>FOR UPDATE</literal>, except that the lock |
| 1307 |
acquired is weaker: this lock will not block |
| 1308 |
<literal>SELECT FOR KEY SHARE</literal> commands that attempt to acquire |
| 1309 |
a lock on the same rows. This lock mode is also acquired by any |
| 1310 |
<command>UPDATE</command> that does not acquire a <literal>FOR UPDATE</literal> lock. |
| 1311 |
</para> |
| 1312 |
</listitem> |
| 1313 |
</varlistentry> |
| 1314 |
|
| 1315 |
<varlistentry> |
| 1316 |
<term> |
| 1317 |
<literal>FOR SHARE</literal> |
| 1318 |
</term> |
| 1319 |
<listitem> |
| 1320 |
<para> |
| 1321 |
Behaves similarly to <literal>FOR NO KEY UPDATE</literal>, except that it |
| 1322 |
acquires a shared lock rather than exclusive lock on each retrieved |
| 1323 |
row. A shared lock blocks other transactions from performing |
| 1324 |
<command>UPDATE</command>, <command>DELETE</command>, |
| 1325 |
<command>SELECT FOR UPDATE</command> or |
| 1326 |
<command>SELECT FOR NO KEY UPDATE</command> on these rows, but it does not |
| 1327 |
prevent them from performing <command>SELECT FOR SHARE</command> or |
| 1328 |
<command>SELECT FOR KEY SHARE</command>. |
| 1329 |
</para> |
| 1330 |
</listitem> |
| 1331 |
</varlistentry> |
| 1332 |
|
| 1333 |
<varlistentry> |
| 1334 |
<term> |
| 1335 |
<literal>FOR KEY SHARE</literal> |
| 1336 |
</term> |
| 1337 |
<listitem> |
| 1338 |
<para> |
| 1339 |
Behaves similarly to <literal>FOR SHARE</literal>, except that the |
| 1340 |
lock is weaker: <literal>SELECT FOR UPDATE</literal> is blocked, but not |
| 1341 |
<literal>SELECT FOR NO KEY UPDATE</literal>. A key-shared lock blocks |
| 1342 |
other transactions from performing <command>DELETE</command> or |
| 1343 |
any <command>UPDATE</command> that changes the key values, but not |
| 1344 |
other <command>UPDATE</command>, and neither does it prevent |
| 1345 |
<command>SELECT FOR NO KEY UPDATE</command>, <command>SELECT FOR SHARE</command>, |
| 1346 |
or <command>SELECT FOR KEY SHARE</command>. |
| 1347 |
</para> |
| 1348 |
</listitem> |
| 1349 |
</varlistentry> |
| 1350 |
</variablelist> |
| 1351 |
|
| 1352 |
<para> |
| 1353 |
<productname>PostgreSQL</productname> doesn't remember any |
| 1354 |
information about modified rows in memory, so there is no limit on |
| 1355 |
the number of rows locked at one time. However, locking a row |
| 1356 |
might cause a disk write, e.g., <command>SELECT FOR |
| 1357 |
UPDATE</command> modifies selected rows to mark them locked, and so |
| 1358 |
will result in disk writes. |
| 1359 |
</para> |
| 1360 |
|
| 1361 |
<table tocentry="1" id="row-lock-compatibility"> |
| 1362 |
<title>Conflicting Row-Level Locks</title> |
| 1363 |
<tgroup cols="5"> |
| 1364 |
<colspec colname="col1" colwidth="1.5*"/> |
| 1365 |
<colspec colname="lockst" colwidth="1*"/> |
| 1366 |
<colspec colname="col3" colwidth="1*"/> |
| 1367 |
<colspec colname="col4" colwidth="1*"/> |
| 1368 |
<colspec colname="lockend" colwidth="1*"/> |
| 1369 |
<spanspec namest="lockst" nameend="lockend" spanname="lockreq"/> |
| 1370 |
<thead> |
| 1371 |
<row> |
| 1372 |
<entry morerows="1">Requested Lock Mode</entry> |
| 1373 |
<entry spanname="lockreq">Current Lock Mode</entry> |
| 1374 |
</row> |
| 1375 |
<row> |
| 1376 |
<entry>FOR KEY SHARE</entry> |
| 1377 |
<entry>FOR SHARE</entry> |
| 1378 |
<entry>FOR NO KEY UPDATE</entry> |
| 1379 |
<entry>FOR UPDATE</entry> |
| 1380 |
</row> |
| 1381 |
</thead> |
| 1382 |
<tbody> |
| 1383 |
<row> |
| 1384 |
<entry>FOR KEY SHARE</entry> |
| 1385 |
<entry align="center"></entry> |
| 1386 |
<entry align="center"></entry> |
| 1387 |
<entry align="center"></entry> |
| 1388 |
<entry align="center">X</entry> |
| 1389 |
</row> |
| 1390 |
<row> |
| 1391 |
<entry>FOR SHARE</entry> |
| 1392 |
<entry align="center"></entry> |
| 1393 |
<entry align="center"></entry> |
| 1394 |
<entry align="center">X</entry> |
| 1395 |
<entry align="center">X</entry> |
| 1396 |
</row> |
| 1397 |
<row> |
| 1398 |
<entry>FOR NO KEY UPDATE</entry> |
| 1399 |
<entry align="center"></entry> |
| 1400 |
<entry align="center">X</entry> |
| 1401 |
<entry align="center">X</entry> |
| 1402 |
<entry align="center">X</entry> |
| 1403 |
</row> |
| 1404 |
<row> |
| 1405 |
<entry>FOR UPDATE</entry> |
| 1406 |
<entry align="center">X</entry> |
| 1407 |
<entry align="center">X</entry> |
| 1408 |
<entry align="center">X</entry> |
| 1409 |
<entry align="center">X</entry> |
| 1410 |
</row> |
| 1411 |
</tbody> |
| 1412 |
</tgroup> |
| 1413 |
</table> |
| 1414 |
</sect2> |
| 1415 |
|
| 1416 |
<sect2 id="locking-pages"> |
| 1417 |
<title>Page-Level Locks</title> |
| 1418 |
|
| 1419 |
<para> |
| 1420 |
In addition to table and row locks, page-level share/exclusive locks are |
| 1421 |
used to control read/write access to table pages in the shared buffer |
| 1422 |
pool. These locks are released immediately after a row is fetched or |
| 1423 |
updated. Application developers normally need not be concerned with |
| 1424 |
page-level locks, but they are mentioned here for completeness. |
| 1425 |
</para> |
| 1426 |
|
| 1427 |
</sect2> |
| 1428 |
|
| 1429 |
<sect2 id="locking-deadlocks"> |
| 1430 |
<title>Deadlocks</title> |
| 1431 |
|
| 1432 |
<indexterm zone="locking-deadlocks"> |
| 1433 |
<primary>deadlock</primary> |
| 1434 |
</indexterm> |
| 1435 |
|
| 1436 |
<para> |
| 1437 |
The use of explicit locking can increase the likelihood of |
| 1438 |
<firstterm>deadlocks</firstterm>, wherein two (or more) transactions each |
| 1439 |
hold locks that the other wants. For example, if transaction 1 |
| 1440 |
acquires an exclusive lock on table A and then tries to acquire |
| 1441 |
an exclusive lock on table B, while transaction 2 has already |
| 1442 |
exclusive-locked table B and now wants an exclusive lock on table |
| 1443 |
A, then neither one can proceed. |
| 1444 |
<productname>PostgreSQL</productname> automatically detects |
| 1445 |
deadlock situations and resolves them by aborting one of the |
| 1446 |
transactions involved, allowing the other(s) to complete. |
| 1447 |
(Exactly which transaction will be aborted is difficult to |
| 1448 |
predict and should not be relied upon.) |
| 1449 |
</para> |
| 1450 |
|
| 1451 |
<para> |
| 1452 |
Note that deadlocks can also occur as the result of row-level |
| 1453 |
locks (and thus, they can occur even if explicit locking is not |
| 1454 |
used). Consider the case in which two concurrent |
| 1455 |
transactions modify a table. The first transaction executes: |
| 1456 |
|
| 1457 |
<screen> |
| 1458 |
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111; |
| 1459 |
</screen> |
| 1460 |
|
| 1461 |
This acquires a row-level lock on the row with the specified |
| 1462 |
account number. Then, the second transaction executes: |
| 1463 |
|
| 1464 |
<screen> |
| 1465 |
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222; |
| 1466 |
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111; |
| 1467 |
</screen> |
| 1468 |
|
| 1469 |
The first <command>UPDATE</command> statement successfully |
| 1470 |
acquires a row-level lock on the specified row, so it succeeds in |
| 1471 |
updating that row. However, the second <command>UPDATE</command> |
| 1472 |
statement finds that the row it is attempting to update has |
| 1473 |
already been locked, so it waits for the transaction that |
| 1474 |
acquired the lock to complete. Transaction two is now waiting on |
| 1475 |
transaction one to complete before it continues execution. Now, |
| 1476 |
transaction one executes: |
| 1477 |
|
| 1478 |
<screen> |
| 1479 |
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222; |
| 1480 |
</screen> |
| 1481 |
|
| 1482 |
Transaction one attempts to acquire a row-level lock on the |
| 1483 |
specified row, but it cannot: transaction two already holds such |
| 1484 |
a lock. So it waits for transaction two to complete. Thus, |
| 1485 |
transaction one is blocked on transaction two, and transaction |
| 1486 |
two is blocked on transaction one: a deadlock |
| 1487 |
condition. <productname>PostgreSQL</productname> will detect this |
| 1488 |
situation and abort one of the transactions. |
| 1489 |
</para> |
| 1490 |
|
| 1491 |
<para> |
| 1492 |
The best defense against deadlocks is generally to avoid them by |
| 1493 |
being certain that all applications using a database acquire |
| 1494 |
locks on multiple objects in a consistent order. In the example |
| 1495 |
above, if both transactions |
| 1496 |
had updated the rows in the same order, no deadlock would have |
| 1497 |
occurred. One should also ensure that the first lock acquired on |
| 1498 |
an object in a transaction is the most restrictive mode that will be |
| 1499 |
needed for that object. If it is not feasible to verify this in |
| 1500 |
advance, then deadlocks can be handled on-the-fly by retrying |
| 1501 |
transactions that abort due to deadlocks. |
| 1502 |
</para> |
| 1503 |
|
| 1504 |
<para> |
| 1505 |
So long as no deadlock situation is detected, a transaction seeking |
| 1506 |
either a table-level or row-level lock will wait indefinitely for |
| 1507 |
conflicting locks to be released. This means it is a bad idea for |
| 1508 |
applications to hold transactions open for long periods of time |
| 1509 |
(e.g., while waiting for user input). |
| 1510 |
</para> |
| 1511 |
</sect2> |
| 1512 |
|
| 1513 |
<sect2 id="advisory-locks"> |
| 1514 |
<title>Advisory Locks</title> |
| 1515 |
|
| 1516 |
<indexterm zone="advisory-locks"> |
| 1517 |
<primary>advisory lock</primary> |
| 1518 |
</indexterm> |
| 1519 |
|
| 1520 |
<indexterm zone="advisory-locks"> |
| 1521 |
<primary>lock</primary> |
| 1522 |
<secondary>advisory</secondary> |
| 1523 |
</indexterm> |
| 1524 |
|
| 1525 |
<para> |
| 1526 |
<productname>PostgreSQL</productname> provides a means for |
| 1527 |
creating locks that have application-defined meanings. These are |
| 1528 |
called <firstterm>advisory locks</firstterm>, because the system does not |
| 1529 |
enforce their use — it is up to the application to use them |
| 1530 |
correctly. Advisory locks can be useful for locking strategies |
| 1531 |
that are an awkward fit for the MVCC model. |
| 1532 |
For example, a common use of advisory locks is to emulate pessimistic |
| 1533 |
locking strategies typical of so-called <quote>flat file</quote> data |
| 1534 |
management systems. |
| 1535 |
While a flag stored in a table could be used for the same purpose, |
| 1536 |
advisory locks are faster, avoid table bloat, and are automatically |
| 1537 |
cleaned up by the server at the end of the session. |
| 1538 |
</para> |
| 1539 |
|
| 1540 |
<para> |
| 1541 |
There are two ways to acquire an advisory lock in |
| 1542 |
<productname>PostgreSQL</productname>: at session level or at |
| 1543 |
transaction level. |
| 1544 |
Once acquired at session level, an advisory lock is held until |
| 1545 |
explicitly released or the session ends. Unlike standard lock requests, |
| 1546 |
session-level advisory lock requests do not honor transaction semantics: |
| 1547 |
a lock acquired during a transaction that is later rolled back will still |
| 1548 |
be held following the rollback, and likewise an unlock is effective even |
| 1549 |
if the calling transaction fails later. A lock can be acquired multiple |
| 1550 |
times by its owning process; for each completed lock request there must |
| 1551 |
be a corresponding unlock request before the lock is actually released. |
| 1552 |
Transaction-level lock requests, on the other hand, behave more like |
| 1553 |
regular lock requests: they are automatically released at the end of the |
| 1554 |
transaction, and there is no explicit unlock operation. This behavior |
| 1555 |
is often more convenient than the session-level behavior for short-term |
| 1556 |
usage of an advisory lock. |
| 1557 |
Session-level and transaction-level lock requests for the same advisory |
| 1558 |
lock identifier will block each other in the expected way. |
| 1559 |
If a session already holds a given advisory lock, additional requests by |
| 1560 |
it will always succeed, even if other sessions are awaiting the lock; this |
| 1561 |
statement is true regardless of whether the existing lock hold and new |
| 1562 |
request are at session level or transaction level. |
| 1563 |
</para> |
| 1564 |
|
| 1565 |
<para> |
| 1566 |
Like all locks in |
| 1567 |
<productname>PostgreSQL</productname>, a complete list of advisory locks |
| 1568 |
currently held by any session can be found in the <link |
| 1569 |
linkend="view-pg-locks"><structname>pg_locks</structname></link> system |
| 1570 |
view. |
| 1571 |
</para> |
| 1572 |
|
| 1573 |
<para> |
| 1574 |
Both advisory locks and regular locks are stored in a shared memory |
| 1575 |
pool whose size is defined by the configuration variables |
| 1576 |
<xref linkend="guc-max-locks-per-transaction"/> and |
| 1577 |
<xref linkend="guc-max-connections"/>. |
| 1578 |
Care must be taken not to exhaust this |
| 1579 |
memory or the server will be unable to grant any locks at all. |
| 1580 |
This imposes an upper limit on the number of advisory locks |
| 1581 |
grantable by the server, typically in the tens to hundreds of thousands |
| 1582 |
depending on how the server is configured. |
| 1583 |
</para> |
| 1584 |
|
| 1585 |
<para> |
| 1586 |
In certain cases using advisory locking methods, especially in queries |
| 1587 |
involving explicit ordering and <literal>LIMIT</literal> clauses, care must be |
| 1588 |
taken to control the locks acquired because of the order in which SQL |
| 1589 |
expressions are evaluated. For example: |
| 1590 |
<screen> |
| 1591 |
SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok |
| 1592 |
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger! |
| 1593 |
SELECT pg_advisory_lock(q.id) FROM |
| 1594 |
( |
| 1595 |
SELECT id FROM foo WHERE id > 12345 LIMIT 100 |
| 1596 |
) q; -- ok |
| 1597 |
</screen> |
| 1598 |
In the above queries, the second form is dangerous because the |
| 1599 |
<literal>LIMIT</literal> is not guaranteed to be applied before the locking |
| 1600 |
function is executed. This might cause some locks to be acquired |
| 1601 |
that the application was not expecting, and hence would fail to release |
| 1602 |
(until it ends the session). |
| 1603 |
From the point of view of the application, such locks |
| 1604 |
would be dangling, although still viewable in |
| 1605 |
<structname>pg_locks</structname>. |
| 1606 |
</para> |
| 1607 |
|
| 1608 |
<para> |
| 1609 |
The functions provided to manipulate advisory locks are described in |
| 1610 |
<xref linkend="functions-advisory-locks"/>. |
| 1611 |
</para> |
| 1612 |
</sect2> |
| 1613 |
|
| 1614 |
</sect1> |
| 1615 |
|
| 1616 |
<sect1 id="applevel-consistency"> |
| 1617 |
<title>Data Consistency Checks at the Application Level</title> |
| 1618 |
|
| 1619 |
<para> |
| 1620 |
It is very difficult to enforce business rules regarding data integrity |
| 1621 |
using Read Committed transactions because the view of the data is |
| 1622 |
shifting with each statement, and even a single statement may not |
| 1623 |
restrict itself to the statement's snapshot if a write conflict occurs. |
| 1624 |
</para> |
| 1625 |
|
| 1626 |
<para> |
| 1627 |
While a Repeatable Read transaction has a stable view of the data |
| 1628 |
throughout its execution, there is a subtle issue with using |
| 1629 |
<acronym>MVCC</acronym> snapshots for data consistency checks, involving |
| 1630 |
something known as <firstterm>read/write conflicts</firstterm>. |
| 1631 |
If one transaction writes data and a concurrent transaction attempts |
| 1632 |
to read the same data (whether before or after the write), it cannot |
| 1633 |
see the work of the other transaction. The reader then appears to have |
| 1634 |
executed first regardless of which started first or which committed |
| 1635 |
first. If that is as far as it goes, there is no problem, but |
| 1636 |
if the reader also writes data which is read by a concurrent transaction |
| 1637 |
there is now a transaction which appears to have run before either of |
| 1638 |
the previously mentioned transactions. If the transaction which appears |
| 1639 |
to have executed last actually commits first, it is very easy for a |
| 1640 |
cycle to appear in a graph of the order of execution of the transactions. |
| 1641 |
When such a cycle appears, integrity checks will not work correctly |
| 1642 |
without some help. |
| 1643 |
</para> |
| 1644 |
|
| 1645 |
<para> |
| 1646 |
As mentioned in <xref linkend="xact-serializable"/>, Serializable |
| 1647 |
transactions are just Repeatable Read transactions which add |
| 1648 |
nonblocking monitoring for dangerous patterns of read/write conflicts. |
| 1649 |
When a pattern is detected which could cause a cycle in the apparent |
| 1650 |
order of execution, one of the transactions involved is rolled back to |
| 1651 |
break the cycle. |
| 1652 |
</para> |
| 1653 |
|
| 1654 |
<sect2 id="serializable-consistency"> |
| 1655 |
<title>Enforcing Consistency with Serializable Transactions</title> |
| 1656 |
|
| 1657 |
<para> |
| 1658 |
If the Serializable transaction isolation level is used for all writes |
| 1659 |
and for all reads which need a consistent view of the data, no other |
| 1660 |
effort is required to ensure consistency. Software from other |
| 1661 |
environments which is written to use serializable transactions to |
| 1662 |
ensure consistency should <quote>just work</quote> in this regard in |
| 1663 |
<productname>PostgreSQL</productname>. |
| 1664 |
</para> |
| 1665 |
|
| 1666 |
<para> |
| 1667 |
When using this technique, it will avoid creating an unnecessary burden |
| 1668 |
for application programmers if the application software goes through a |
| 1669 |
framework which automatically retries transactions which are rolled |
| 1670 |
back with a serialization failure. It may be a good idea to set |
| 1671 |
<literal>default_transaction_isolation</literal> to <literal>serializable</literal>. |
| 1672 |
It would also be wise to take some action to ensure that no other |
| 1673 |
transaction isolation level is used, either inadvertently or to |
| 1674 |
subvert integrity checks, through checks of the transaction isolation |
| 1675 |
level in triggers. |
| 1676 |
</para> |
| 1677 |
|
| 1678 |
<para> |
| 1679 |
See <xref linkend="xact-serializable"/> for performance suggestions. |
| 1680 |
</para> |
| 1681 |
|
| 1682 |
<warning> |
| 1683 |
<title>Warning: Serializable Transactions and Data Replication</title> |
| 1684 |
<para> |
| 1685 |
This level of integrity protection using Serializable transactions |
| 1686 |
does not yet extend to hot standby mode (<xref linkend="hot-standby"/>) |
| 1687 |
or logical replicas. |
| 1688 |
Because of that, those using hot standby or logical replication |
| 1689 |
may want to use Repeatable Read and explicit locking on the primary. |
| 1690 |
</para> |
| 1691 |
</warning> |
| 1692 |
</sect2> |
| 1693 |
|
| 1694 |
<sect2 id="non-serializable-consistency"> |
| 1695 |
<title>Enforcing Consistency with Explicit Blocking Locks</title> |
| 1696 |
|
| 1697 |
<para> |
| 1698 |
When non-serializable writes are possible, |
| 1699 |
to ensure the current validity of a row and protect it against |
| 1700 |
concurrent updates one must use <command>SELECT FOR UPDATE</command>, |
| 1701 |
<command>SELECT FOR SHARE</command>, or an appropriate <command>LOCK |
| 1702 |
TABLE</command> statement. (<command>SELECT FOR UPDATE</command> |
| 1703 |
and <command>SELECT FOR SHARE</command> lock just the |
| 1704 |
returned rows against concurrent updates, while <command>LOCK |
| 1705 |
TABLE</command> locks the whole table.) This should be taken into |
| 1706 |
account when porting applications to |
| 1707 |
<productname>PostgreSQL</productname> from other environments. |
| 1708 |
</para> |
| 1709 |
|
| 1710 |
<para> |
| 1711 |
Also of note to those converting from other environments is the fact |
| 1712 |
that <command>SELECT FOR UPDATE</command> does not ensure that a |
| 1713 |
concurrent transaction will not update or delete a selected row. |
| 1714 |
To do that in <productname>PostgreSQL</productname> you must actually |
| 1715 |
update the row, even if no values need to be changed. |
| 1716 |
<command>SELECT FOR UPDATE</command> <emphasis>temporarily blocks</emphasis> |
| 1717 |
other transactions from acquiring the same lock or executing an |
| 1718 |
<command>UPDATE</command> or <command>DELETE</command> which would |
| 1719 |
affect the locked row, but once the transaction holding this lock |
| 1720 |
commits or rolls back, a blocked transaction will proceed with the |
| 1721 |
conflicting operation unless an actual <command>UPDATE</command> of |
| 1722 |
the row was performed while the lock was held. |
| 1723 |
</para> |
| 1724 |
|
| 1725 |
<para> |
| 1726 |
Global validity checks require extra thought under |
| 1727 |
non-serializable <acronym>MVCC</acronym>. |
| 1728 |
For example, a banking application might wish to check that the sum of |
| 1729 |
all credits in one table equals the sum of debits in another table, |
| 1730 |
when both tables are being actively updated. Comparing the results of two |
| 1731 |
successive <literal>SELECT sum(...)</literal> commands will not work reliably in |
| 1732 |
Read Committed mode, since the second query will likely include the results |
| 1733 |
of transactions not counted by the first. Doing the two sums in a |
| 1734 |
single repeatable read transaction will give an accurate picture of only the |
| 1735 |
effects of transactions that committed before the repeatable read transaction |
| 1736 |
started — but one might legitimately wonder whether the answer is still |
| 1737 |
relevant by the time it is delivered. If the repeatable read transaction |
| 1738 |
itself applied some changes before trying to make the consistency check, |
| 1739 |
the usefulness of the check becomes even more debatable, since now it |
| 1740 |
includes some but not all post-transaction-start changes. In such cases |
| 1741 |
a careful person might wish to lock all tables needed for the check, |
| 1742 |
in order to get an indisputable picture of current reality. A |
| 1743 |
<literal>SHARE</literal> mode (or higher) lock guarantees that there are no |
| 1744 |
uncommitted changes in the locked table, other than those of the current |
| 1745 |
transaction. |
| 1746 |
</para> |
| 1747 |
|
| 1748 |
<para> |
| 1749 |
Note also that if one is relying on explicit locking to prevent concurrent |
| 1750 |
changes, one should either use Read Committed mode, or in Repeatable Read |
| 1751 |
mode be careful to obtain |
| 1752 |
locks before performing queries. A lock obtained by a |
| 1753 |
repeatable read transaction guarantees that no other transactions modifying |
| 1754 |
the table are still running, but if the snapshot seen by the |
| 1755 |
transaction predates obtaining the lock, it might predate some now-committed |
| 1756 |
changes in the table. A repeatable read transaction's snapshot is actually |
| 1757 |
frozen at the start of its first query or data-modification command |
| 1758 |
(<literal>SELECT</literal>, <literal>INSERT</literal>, |
| 1759 |
<literal>UPDATE</literal>, <literal>DELETE</literal>, or |
| 1760 |
<literal>MERGE</literal>), so it is possible to obtain locks explicitly |
| 1761 |
before the snapshot is frozen. |
| 1762 |
</para> |
| 1763 |
</sect2> |
| 1764 |
</sect1> |
| 1765 |
|
| 1766 |
<sect1 id="mvcc-serialization-failure-handling"> |
| 1767 |
<title>Serialization Failure Handling</title> |
| 1768 |
|
| 1769 |
<indexterm> |
| 1770 |
<primary>serialization failure</primary> |
| 1771 |
</indexterm> |
| 1772 |
<indexterm> |
| 1773 |
<primary>retryable error</primary> |
| 1774 |
</indexterm> |
| 1775 |
|
| 1776 |
<para> |
| 1777 |
Both Repeatable Read and Serializable isolation levels can produce |
| 1778 |
errors that are designed to prevent serialization anomalies. As |
| 1779 |
previously stated, applications using these levels must be prepared to |
| 1780 |
retry transactions that fail due to serialization errors. Such an |
| 1781 |
error's message text will vary according to the precise circumstances, |
| 1782 |
but it will always have the SQLSTATE code <literal>40001</literal> |
| 1783 |
(<literal>serialization_failure</literal>). |
| 1784 |
</para> |
| 1785 |
|
| 1786 |
<para> |
| 1787 |
It may also be advisable to retry deadlock failures. |
| 1788 |
These have the SQLSTATE code <literal>40P01</literal> |
| 1789 |
(<literal>deadlock_detected</literal>). |
| 1790 |
</para> |
| 1791 |
|
| 1792 |
<para> |
| 1793 |
In some cases it is also appropriate to retry unique-key failures, |
| 1794 |
which have SQLSTATE code <literal>23505</literal> |
| 1795 |
(<literal>unique_violation</literal>), and exclusion constraint |
| 1796 |
failures, which have SQLSTATE code <literal>23P01</literal> |
| 1797 |
(<literal>exclusion_violation</literal>). For example, if the |
| 1798 |
application selects a new value for a primary key column after |
| 1799 |
inspecting the currently stored keys, it could get a unique-key |
| 1800 |
failure because another application instance selected the same new key |
| 1801 |
concurrently. This is effectively a serialization failure, but the |
| 1802 |
server will not detect it as such because it cannot <quote>see</quote> |
| 1803 |
the connection between the inserted value and the previous reads. |
| 1804 |
There are also some corner cases in which the server will issue a |
| 1805 |
unique-key or exclusion constraint error even though in principle it |
| 1806 |
has enough information to determine that a serialization problem |
| 1807 |
is the underlying cause. While it's recommendable to just |
| 1808 |
retry <literal>serialization_failure</literal> errors unconditionally, |
| 1809 |
more care is needed when retrying these other error codes, since they |
| 1810 |
might represent persistent error conditions rather than transient |
| 1811 |
failures. |
| 1812 |
</para> |
| 1813 |
|
| 1814 |
<para> |
| 1815 |
It is important to retry the complete transaction, including all logic |
| 1816 |
that decides which SQL to issue and/or which values to use. |
| 1817 |
Therefore, <productname>PostgreSQL</productname> does not offer an |
| 1818 |
automatic retry facility, since it cannot do so with any guarantee of |
| 1819 |
correctness. |
| 1820 |
</para> |
| 1821 |
|
| 1822 |
<para> |
| 1823 |
Transaction retry does not guarantee that the retried transaction will |
| 1824 |
complete; multiple retries may be needed. In cases with very high |
| 1825 |
contention, it is possible that completion of a transaction may take |
| 1826 |
many attempts. In cases involving a conflicting prepared transaction, |
| 1827 |
it may not be possible to make progress until the prepared transaction |
| 1828 |
commits or rolls back. |
| 1829 |
</para> |
| 1830 |
</sect1> |
| 1831 |
|
| 1832 |
<sect1 id="mvcc-caveats"> |
| 1833 |
<title>Caveats</title> |
| 1834 |
|
| 1835 |
<para> |
| 1836 |
Some DDL commands, currently only <link linkend="sql-truncate"><command>TRUNCATE</command></link> and the |
| 1837 |
table-rewriting forms of <link linkend="sql-altertable"><command>ALTER TABLE</command></link>, are not |
| 1838 |
MVCC-safe. This means that after the truncation or rewrite commits, the |
| 1839 |
table will appear empty to concurrent transactions, if they are using a |
| 1840 |
snapshot taken before the DDL command committed. This will only be an |
| 1841 |
issue for a transaction that did not access the table in question |
| 1842 |
before the DDL command started — any transaction that has done so |
| 1843 |
would hold at least an <literal>ACCESS SHARE</literal> table lock, |
| 1844 |
which would block the DDL command until that transaction completes. |
| 1845 |
So these commands will not cause any apparent inconsistency in the |
| 1846 |
table contents for successive queries on the target table, but they |
| 1847 |
could cause visible inconsistency between the contents of the target |
| 1848 |
table and other tables in the database. |
| 1849 |
</para> |
| 1850 |
|
| 1851 |
<para> |
| 1852 |
Support for the Serializable transaction isolation level has not yet |
| 1853 |
been added to hot standby replication targets (described in |
| 1854 |
<xref linkend="hot-standby"/>). The strictest isolation level currently |
| 1855 |
supported in hot standby mode is Repeatable Read. While performing all |
| 1856 |
permanent database writes within Serializable transactions on the |
| 1857 |
primary will ensure that all standbys will eventually reach a consistent |
| 1858 |
state, a Repeatable Read transaction run on the standby can sometimes |
| 1859 |
see a transient state that is inconsistent with any serial execution |
| 1860 |
of the transactions on the primary. |
| 1861 |
</para> |
| 1862 |
|
| 1863 |
<para> |
| 1864 |
Internal access to the system catalogs is not done using the isolation |
| 1865 |
level of the current transaction. This means that newly created database |
| 1866 |
objects such as tables are visible to concurrent Repeatable Read and |
| 1867 |
Serializable transactions, even though the rows they contain are not. In |
| 1868 |
contrast, queries that explicitly examine the system catalogs don't see |
| 1869 |
rows representing concurrently created database objects, in the higher |
| 1870 |
isolation levels. |
| 1871 |
</para> |
| 1872 |
</sect1> |
| 1873 |
|
| 1874 |
<sect1 id="locking-indexes"> |
| 1875 |
<title>Locking and Indexes</title> |
| 1876 |
|
| 1877 |
<indexterm zone="locking-indexes"> |
| 1878 |
<primary>index</primary> |
| 1879 |
<secondary>locks</secondary> |
| 1880 |
</indexterm> |
| 1881 |
|
| 1882 |
<para> |
| 1883 |
Though <productname>PostgreSQL</productname> |
| 1884 |
provides nonblocking read/write access to table |
| 1885 |
data, nonblocking read/write access is not currently offered for every |
| 1886 |
index access method implemented |
| 1887 |
in <productname>PostgreSQL</productname>. |
| 1888 |
The various index types are handled as follows: |
| 1889 |
|
| 1890 |
<variablelist> |
| 1891 |
<varlistentry> |
| 1892 |
<term> |
| 1893 |
B-tree, <acronym>GiST</acronym> and <acronym>SP-GiST</acronym> indexes |
| 1894 |
</term> |
| 1895 |
<listitem> |
| 1896 |
<para> |
| 1897 |
Short-term share/exclusive page-level locks are used for |
| 1898 |
read/write access. Locks are released immediately after each |
| 1899 |
index row is fetched or inserted. These index types provide |
| 1900 |
the highest concurrency without deadlock conditions. |
| 1901 |
</para> |
| 1902 |
</listitem> |
| 1903 |
</varlistentry> |
| 1904 |
|
| 1905 |
<varlistentry> |
| 1906 |
<term> |
| 1907 |
Hash indexes |
| 1908 |
</term> |
| 1909 |
<listitem> |
| 1910 |
<para> |
| 1911 |
Share/exclusive hash-bucket-level locks are used for read/write |
| 1912 |
access. Locks are released after the whole bucket is processed. |
| 1913 |
Bucket-level locks provide better concurrency than index-level |
| 1914 |
ones, but deadlock is possible since the locks are held longer |
| 1915 |
than one index operation. |
| 1916 |
</para> |
| 1917 |
</listitem> |
| 1918 |
</varlistentry> |
| 1919 |
|
| 1920 |
<varlistentry> |
| 1921 |
<term> |
| 1922 |
<acronym>GIN</acronym> indexes |
| 1923 |
</term> |
| 1924 |
<listitem> |
| 1925 |
<para> |
| 1926 |
Short-term share/exclusive page-level locks are used for |
| 1927 |
read/write access. Locks are released immediately after each |
| 1928 |
index row is fetched or inserted. But note that insertion of a |
| 1929 |
GIN-indexed value usually produces several index key insertions |
| 1930 |
per row, so GIN might do substantial work for a single value's |
| 1931 |
insertion. |
| 1932 |
</para> |
| 1933 |
</listitem> |
| 1934 |
</varlistentry> |
| 1935 |
</variablelist> |
| 1936 |
</para> |
| 1937 |
|
| 1938 |
<para> |
| 1939 |
Currently, B-tree indexes offer the best performance for concurrent |
| 1940 |
applications; since they also have more features than hash |
| 1941 |
indexes, they are the recommended index type for concurrent |
| 1942 |
applications that need to index scalar data. When dealing with |
| 1943 |
non-scalar data, B-trees are not useful, and GiST, SP-GiST or GIN |
| 1944 |
indexes should be used instead. |
| 1945 |
</para> |
| 1946 |
</sect1> |
| 1947 |
</chapter> |