Page 1 of 1
Mysql doesn't nest transactions?
Posted: Tue Dec 08, 2009 5:09 pm
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
Re: Mysql doesn't nest transactions?
Posted: Wed Dec 09, 2009 4:27 pm
by VladSun
From my point of view, switching to PGSQL is always better
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

Re: Mysql doesn't nest transactions?
Posted: Wed Dec 09, 2009 4:38 pm
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