Mysql doesn't nest transactions?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Mysql doesn't nest transactions?

Post by josh »

Which databases support this? I have code that uses transactions to make sure a series of saves (inserts) all take place. I want to test this code by wrapping my test in another transaction, but it appears as soon as my "inner" transaction commits, I see the data from another connection immediately, and the outer rollback has no effect? What should I do switch to postresql or something?

I found Code Ignitor solved this nicely http://codeigniter.com/forums/viewthread/82771/
I wrote an equivalent for Zend Framework's mysqli adapter http://framework.zend.com/issues/browse ... t-tabpanel
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Mysql doesn't nest transactions?

Post by VladSun »

:offtopic:
From my point of view, switching to PGSQL is always better :)
:P

Hm... Maybe I am wrong but at first look I think the CI "patchwork" solved this only for a single level of nesting :

function trans_begin($test_mode = FALSE)

Code: Select all

$this->_trans_depth=1;  //LINE UPDATED
function trans_commit()

Code: Select all

$this->_trans_depth=0;  //LINE UPDATED
I suppose it should be something like:

function trans_begin($test_mode = FALSE)

Code: Select all

$this->_trans_depth++;  //LINE UPDATED
function trans_commit()

Code: Select all

$this->_trans_depth--;  //LINE UPDATED
I haven't dig your code in depth (EDIT: In fact, I even haven't looked at it - please forgive me :) ), so I don't know whether you've fixed this or not. My apologies if you have :)

EDIT: OK, you've solved it nicely :)
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Mysql doesn't nest transactions?

Post by josh »

Cool, I will give serious looks into psql for future projects.
I liked that Zend made it SUPER easy to wrap the adapter, in the event they don't accept my patch

Code: Select all

<?php
class Ne8_Db_ShufflerZendAdapter extends Shuffler_Mapper_Adapter_ZendDb
{
    /**
     * Keeps track of transaction nest level, to emulate mysql support, -1 meaning no transaction
     * has begun, 0 meaning there is no nesting, 1 meaning there are 2 transactions, ad infintum
     * 
     * @var integer
     */
    protected $_transaction_depth = -1;
    
    protected $_should_emulate_nesting = true;
    
    /**
     * Leave autocommit mode and begin a transaction.
     *
     * @return bool True
     */
    public function beginTransaction()
    {
        $this->_transaction_depth++;
        return parent::beginTransaction();
    }
    
    /**
     * Commit last opened transaction
     *
     * @return bool True
     */
    public function commit()
    {
        $this->_transaction_depth--;
        if( $this->shouldEmulateNesting() )
        {
            return;
        }
        return parent::commit();
    }
    
    /**
     * Roll back a transaction and return to autocommit mode.
     *
     * @return bool True
     */
    public function rollBack()
    {
        $this->_transaction_depth--;
        if( $this->shouldEmulateNesting() )
        {
            return;
        }
        return parent::rollBack();
    }
    
    protected function shouldEmulateNesting()
    {
        return $this->_should_emulate_nesting && $this->isNested();   
    }
    
    protected function isNested()
    {
        return $this->_transaction_depth >= 0;
    }
}
Technically this wraps the data shuffler adapter for Zend, which in turn wraps the zend adapter
Post Reply