postgres support for serializable transactions instead of just read committed

[ghost]

Created by Dieter Plaetinck, 6th Apr 2010. (originally Lighthouse ticket #548):

---

Postgres supports 2 kinds of transactions, see
http://www.postgresql.org/docs/8.4/interactive/mvcc.html

• read committed (default on 'BEGIN')
• serializable

There currently is no proper way to do serializable transaction queries on postgres with cakephp.
There are two ways to make a transaction serializable:

• 'BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
• 'START TRANSACTION ISOLATION LEVEL SERIALIZABLE'

see
http://www.postgresql.org/docs/7.4/interactive/sql-set-transaction.html
http://www.postgresql.org/docs/8.4/interactive/sql-start-transaction.html

In an application, you can just do

$Model->query('START TRANSACTION ISOLATION LEVEL SERIALIZABLE');


but it would be nice if cake had support out of the box for this.
I would submit a patch myself, but I'm not sure how i should implement this. any pointers?
But the fix may be more trivial then the explanation, so feel free :)

Dieter

[ghost]

6th Apr 2010, Dieter Plaetinck said:

---

Scratch the "in an application, you can just do" part.
datasource->$_transactionStarted must be set to true. you don't want to know how long it took me to figure that out. oh well :)

[ghost]

7th Apr 2010, Jose Lorenzo Rodríguez said:

---

Dieter,

Thanks for your suggestion, it will be taken in account for next releases. Meanwhile, and contrary to what you have explained in your follow up response, it is possible to do it in the application level.

Let's suppose you have model Post, you can initiate the transaction this way: $Post->getDataSource()->begin($Post); and then set the transaction type by yourself doing $Post->query('START TRANSACTION ISOLATION LEVEL SERIALIZABLE');

No need to scratch that part :)

Thanks again your suggestion

[ghost]

7th Apr 2010, Dieter Plaetinck said:

---

yep you're right. but it's 'SET' not 'START'

[ghost]

10th Apr 2010, Stephen Cuppett said:

---

Here's a tiny patch that implements this. Do not have any tests yet, but it was pretty trivial.

It adds a datasource configuration parameter "serializable" (true|false, default false). Not sure if the term is good or not.

I also noticed that _commands is duplicated in DboPostgres from DboSource unnecessarily, took that out so the patch actually results in -7 lines of code.

[ghost]

10th Apr 2010, Stephen Cuppett said:

---

Sorry, didn't pay close enough attention to the doc. The SET command is only valid inside a transaction block, not for the whole connection.

Not sure the real desire, a parameter could be added to the Model->begin($serializable = false) method to override the one in DboSource or I've attached another patch that instead just updates the $_command for begin to the START command using the same datasource connection parameter.

[ghost]

12th Apr 2010, Dieter Plaetinck said:

---

the serializable property should definitely be changeable on a per-transaction basis.
I wrote a simple postgres-specific behavior for my app:
http://bin.cakephp.org/view/247652215
it takes care of the serializable settings, handles nested transactions (savepoints per model) and supports "upgrading" to serializable within a nested transaction.
it requires the following patch:
http://github.com/kangaroot/cakephp1x/commit/d5eb8f0ddffc22dff989f15ae563f7fac31cbd1d

[lorenzo]

closing as duplicate of #3136