Table of Contents
The transaction system is a core component of a relational database. It provides services that facilitate developing applications that guarantee data integrity. The SQL standard regulates some of the features of database transactions, but leaves many details unspecified. As a consequence, the transaction systems of relational databases can differ substantially. These days, many people are trying to get away from Oracle database and move to PostgreSQL. To migrate an application from Oracle to PostgreSQL, it is crucial to understand the differences between the respective transaction systems. Otherwise, you can end up with nasty surprises that may jeopardize performance and data integrity. So I thought it would be useful to compile a comparison of the features of transactions in Oracle and PostgreSQL.
No, this is neither about chemistry nor about drug abuse. The acronym ACID stands for
We'll look at these categories in detail further down.
First, it may be useful to describe the aspects of transaction management that work identical in Oracle and PostgreSQL. Fortunately, many important properties fall into this nice category:
SELECT ... FOR UPDATE for explicit concurrency control. For a discussion of the differences, read on.READ COMMITTED as default transaction isolation level, which behaves very similar on both systems.There are a number of subtle differences in how atomicity works in both databases:
In Oracle, any DML statement will implicitly start a database transaction, unless there is already a transaction open. You have to end these transactions explicitly with COMMIT or ROLLBACK. There is no specific statement to start a transaction.
In contrast, PostgreSQL operates in autocommit mode: unless you explicitly start a multi-statement transaction with START TRANSACTION or BEGIN, each statement runs in its own transaction. At the end of such a single-statement transaction, PostgreSQL automatically performs a COMMIT.
Many database APIs allow you to turn autocommit off. Since the PostgreSQL server does not support disabling autocommit, the client emulates it by automatically sending BEGIN as appropriate. With an API like that, you won't have to worry about the difference.
In Oracle, an SQL statement that causes an error does not abort the transaction. Rather, Oracle rolls back the effects of the failed statement, and the transaction can continue. To roll back the transaction, you have to handle the error and actively call ROLLBACK.
PostgreSQL, on the other hand, aborts the whole transaction if an SQL statement in the transaction causes an error. It will ignore all the following statements in an aborted transaction until you end the transaction with ROLLBACK or COMMIT (both will roll back the transaction).
Most well-written applications won't have a problem with that difference, since you normally want to roll back any transaction that caused an error. However, PostgreSQL's behavior can be annoying in certain situations: imagine a long running batch job where bad input data could cause an error. You probably want to be able to handle the error without having to roll back everything you have done so far. In a situation like this, you should use (SQL standard conforming) savepoints in PostgreSQL. This may require certain modifications to your application. Note that you should use savepoints in PostgreSQL sparingly: they are implemented with subtransactions, which can severely affect performance.
In Oracle database, any DDL statement automatically performs a COMMIT, so there is no way to roll back DDL statements.
There is no such restriction in PostgreSQL. With a few exceptions (VACUUM, CREATE DATABASE, CREATE INDEX CONCURRENTLY etc.), you can roll back any SQL statement.
There are few differences in this area; both Oracle and PostgreSQL make sure that no transaction violates constraints.
Perhaps it is worth mentioning that Oracle allows you to enable and disable constraints with ALTER TABLE. You can for example disable a constraint, perform data modifications that violate the constraint and then enable it with ENABLE NOVALIDATE (for primary key and unique constraints, that only works if they are DEFERRABLE). In PostgreSQL, only a superuser can disable the triggers that implement foreign key and deferrable unique and primary key constraints. It also takes a superuser to set session_replication_role = replica, which is another way to disable such triggers.
The following SQL script runs without errors in Oracle database:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tab (id NUMBER PRIMARY KEY); INSERT INTO tab (id) VALUES (1); INSERT INTO tab (id) VALUES (2); COMMIT; UPDATE tab SET id = id + 1; COMMIT; |
The equivalent script causes an error in PostgreSQL:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tab (id numeric PRIMARY KEY); INSERT INTO tab (id) VALUES (1); INSERT INTO tab (id) VALUES (2); UPDATE tab SET id = id + 1; ERROR: duplicate key value violates unique constraint "tab_pkey" DETAIL: Key (id)=(2) already exists. |
The reason is that PostgreSQL (in violation of the SQL standard) checks the constraint after each row, while Oracle checks it at the end of the statement. To make PostgreSQL behave the same as Oracle, create the constraint as DEFERRABLE. Then PostgreSQL will check it at the end of the statement.
This is perhaps the area where Oracle and PostgreSQL differ most. Oracle's support for transaction isolation is rather limited.
The SQL standard defines four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. But compared to its usual level of detail, the individual levels are defined in a sloppy fashion. For example, the standard says that “dirty reads” (of uncommitted data from other transactions) are “possible” at the isolation level READ UNCOMMITTED, but it is not clear whether that is a requirement or not.
Oracle offers only the isolation levels READ COMMITTED and SERIALIZABLE. However, the latter is a lie; Oracle offers snapshot isolation. For example, the following concurrent transactions both succeed (the second session is indented):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE TABLE tab (name VARCHAR2(50), is_highlander NUMBER(1) NOT NULL); -- start a new serializable transaction SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM tab WHERE is_highlander = 1; COUNT(*) ---------- 0 -- start a new serializable transaction SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM tab WHERE is_highlander = 1; COUNT(*) ---------- 0 -- the count is zero, so let's proceed INSERT INTO tab VALUES ('MacLeod', 1); COMMIT; -- the count is zero, so let's proceed INSERT INTO tab VALUES ('Kurgan', 1); COMMIT; |
No serial execution of the transactions would have given the same result: in the second one, the count would have become 1.
Apart from being incorrect, Oracle's implementation is shoddy. For example, if you create a table without saying SEGMENT CREATION IMMEDIATE and then try to insert the first row in a SERIALIZABLE transaction, you will get a serialization error. This is technically legal, as you have to be ready to accept serialization errors on higher isolation levels. But it seems like Oracle throws serialization errors whenever something, like an index page split, was too cumbersome to implement correctly. Essentially, SERIALIZABLE is almost unusable in Oracle database.
PostgreSQL allows all four isolation levels, but it silently upgrades READ UNCOMMITTED to READ COMMITTED (which may or may not be OK by the SQL standard). SERIALIZABLE is truly serializable. PostgreSQL's REPEATABLE READ behaves like Oracle's SERIALIZABLE, except that it actually works well.
READ COMMITTED in PostgreSQL and OracleThe default transaction isolation level READ COMMITTED is a low isolation level. That means that many anomalies can still occur. I described one such anomaly in a previous article. I don't want to repeat the whole matter here: essentially, the situation is the following:
SELECT ... FOR UPDATE) and hangsNow what results will the second transaction get? In both Oracle and PostgreSQL, you get to see the latest committed data if you use READ COMMITTED, but there is a subtle difference:
Both database systems implement durability with a transaction log (called “REDO log” in Oracle and “write-ahead log” in PostgreSQL). Oracle and PostgreSQL provide the same guarantees in this area.
There are some other areas where a comparison between Oracle and PostgreSQL shows differences:
Differences in this area are caused by the radically different way in which Oracle and PostgreSQL implement multiversioning. Oracle has an UNDO tablespace to which it copies the old versions of modified rows, while PostgreSQL keeps several versions of a row in the table itself.
Because of that, the number of data modifications in an Oracle transaction is limited by the size of the UNDO tablespace. For mass deletes or updates, it is common Oracle practice to perform them in batches and COMMIT in between. In PostgreSQL, there is no such limitation, but mass updates will “bloat” a table, so you may also want to update in batches (and run VACUUM in between). However, there is no reason to limit the size of a mass delete in PostgreSQL.
Long running database transactions are a problem in every relational database, as they hold locks and increase the chance of blocking other sessions. Long transactions are also more susceptible to deadlocks. In PostgreSQL, long running transactions are more problematic than in Oracle, because they can also stall the progress of the “autovacuum” maintenance job. This can lead to table bloat that is cumbersome to fix.
SELECT ... FOR UPDATE in Oracle and PostgreSQLBoth database systems know this command to read and lock a table row at the same time. Also, both Oracle and PostgreSQL know the NOWAIT and SKIP LOCKED clauses. PostgreSQL is lacking the WAIT <integer> clause; you have to dynamically adjust the parameter lock_timeout for that.
The most important difference is that in PostgreSQL, FOR UPDATE is not what you should use if you intend to update a row. Unless you plan to delete a row or modify a primary or unique key column, the correct lock mode is FOR NO KEY UPDATE.
Transaction ID wraparound only exists in PostgreSQL. PostgreSQL's implementation of multiversioning manages the visibility of row versions by storing transaction IDs with each row. These numbers are generated from a 32-bit counter and will eventually wrap around. PostgreSQL has to perform special maintenance operations to avoid data loss during such a wraparound. On highly transactional systems, this can become a headache that needs special attention and tuning.
In most aspects, transactions work quite similar in Oracle and PostgreSQL. But there are differences, and you should review these differences if you plan to migrate to PostgreSQL. The comparison in this article will help you spot potential problems during such a migration.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Loved this article. 🙂