Serializable isolation violation on paraccel
Posted by Diego em Julho 3, 2014
I had a problem the other day on concurrent transactions where I got the message
Serializable isolation violation on table – 252092, transactions forming the cycle are: 228199, 228204 (pid:31259)
–BTW, that is the process ID of the second transaction
What happened was that two transactions (T1 and T2) were concurring for the same table.T1 started at 08:53:53 and it finished at 08:54:08. In the meantime, at 08:54:00, T2 started (when T1 was still running) and the failure happened at 08:54:41 (when T1 already committed).
It turns out that under Paraccel’s isolation model, a transaction will be aborted when a commit would violate serializable isolation. That’s what happened when T2 tried to insert into a table that T1 had modified (the update statement)
This sort of behaviour is the consequence of trade-offs which in the case of PADB are made to make read workloads (OLAP) very efficient, at the expense of functionality that is more commonly associated with OLTP, like concurrent updates (read-write) on the same tables.
A work around for this situation is to lock the table before writing to it (the lock will be released in the end of the transaction). There are several different lock types, I particularly use:
lock table <my_table> in exclusive mode;
Which locks the table for writings but not for readings. There’s a more restrictive ones that locks for both read and write.
Also, here are some important points regarding isolation (they were copied from different parts of the admin book):
· If you want a particular query to wait for a commit from another write operation, you have to schedule it accordingly.
· Two transactions are considered to be concurrent if the second one starts before the first commits. Concurrent operations can originate from different sessions controlled by the same user or by different user sessions.
· PADB supports a default "automatic commit" behaviour where each separately executed SQL command commits individually. However, if you enclose a set of commands in a transaction block (defined by BEGIN and END statements), the block commits as one transaction, and so you can roll it back if necessary. An exception to this behaviour is the TRUNCATE command, which automatically commits all outstanding changes made in the current transaction without requiring an END statement
· Concurrent write operations are supported in PADB in a protective way, using write locks on tables and the principle of serializable isolation. Serializable isolation preserves the illusion that a transaction running against a table is the only transaction that is running against that table.
· If two concurrent transactions would produce the same result (by running on their one), they can run safely, if not, the one that would break the serializability is aborted and rolled back.
· System catalog tables and PADB tables (STL and STV) are not locked by transactions, so DDL changes and TRUNCATE statements are visible to any transactions.
· COPY and INSERT operations against the same table are held in wait state until the lock is released, then they proceed as normal
o In practice, although multiple transactions can be running at any given time, only one transaction can write to a single table at a time. The transactions work in isolation; they cannot both copy rows into the table at the same time.
o If two transactions need to write to the same table concurrently, the one that is first to do the write operation will execute, and the other one will wait to perform its updates until the first completes.
o But If T1 Writes to table A, T2 writes to table B (each creating a lock on the tables) and then T1 tries to write to table B and T2 tries to write to table A, a deadlock will occur;