Nested DB transactions

[eluser]lolmann[/eluser]
That fixed it for me, too.

Took me some time to find the problem.

Thanks!

[eluser]SPeed_FANat1c[/eluser]
I checked CI 2.1.3 and stil it is

Code:

/**
  * Start Transaction
  *
  * @access public
  * @return void
  */
function trans_start($test_mode = FALSE)
{
  if ( ! $this->trans_enabled)
  {
   return FALSE;
  }

  // When transactions are nested we only begin/commit/rollback the outermost ones
  if ($this->_trans_depth > 0)
  {
   $this->_trans_depth += 1;
   return;
  }

  $this->trans_begin($test_mode);
}

So was this not fixed or is this not a bug. I cannot understand.
I did test function:

Code:

function test_trans() {

  echo $this->db->_trans_depth;

  $this->db->trans_start();

  echo $this->db->_trans_depth;

  $this->db->trans_start();

  //.print_r($query->result_array());

  $this->Dogs_8_model->trans_test();

  echo $this->db->_trans_depth;
  
  $query = $this->db->query('Select * from logs order by id desc limit 10');

  $this->db->trans_complete();

  if ($this->db->trans_status() === FALSE)
  {
      // generate an error... or use the log_message() function to log your error
  }
  else {
   echo 'ok';
  }
}

and model function:

Code:

public function trans_test() {

  $this->db->trans_start();
   echo 'model' . $this->db->_trans_depth . 'modelend-';
  //$this->db->trans_complete();
}

And everytime trans depth is 0. But in reality postgre log file shows WARNING

2012-11-08 16:22:20 EET WARNING: there is already a transaction in progress

so it tries nested transaction which does not work in PostgreSQL. I completely do not understand.

I started searching because in our application we are getting such warnings. So I want to detect where are they. I think transaction depth should be more than 0 when there is attempt to start inside transaction, but as I see from my test functions I cannot detect that.

I dont't even understand from the documentation if ci supports nested transactions or not:

Traditionally, transactions have required a fair amount of work to implement since they demand that you to keep track of your queries and determine whether to commit or rollback based on the success or failure of your queries. This is particularly cumbersome with nested queries. In contrast, we've implemented a smart transaction system that does all this for you automatically (you can also manage your transactions manually if you choose to, but there's really no benefit).

It mentions nested queries. Nested queries = nested transactions ? From the code snippets it looks that it should not support. When trans deph is > 0 - return, - so do not start another transaction. But it starts because depth does not get over 0 anytime.

From what I found by googling - it looks like it does not support them, but supports savepoints which I don't know well what they differ from transactions yet.

http://www.postgresql.org/docs/current/i...tions.html
http://postgresql.1045698.n5.nabble.com/...25789.html

But then if it does not support - what does this comment mean:

// When transactions are nested we only begin/commit/rollback the outermost ones

So transactions still can be nested in CI? And same is in postgre_driver.php

Is this a bug not fixed for such long time or is this not a bug?

Also - what strategy to use to catch where those transactions become nested when they should not be? In our application we had no intention of doing nested transactions, but could make bugs.
For example we get 2 messages in a row in logs:

2012-11-08 08:51:02 EET WARNING: there is already a transaction in progress
2012-11-08 08:51:02 EET WARNING: there is no transaction in progress

So it looks like it tries to start inside transaction, but postgre does not allow to do that. Then code does not break, it continues and reaches trans_complete. Then interesting - where that starting transaction finishes? If we look at time, its at the same second. Mayeb could be something like this:

Code:

some_function() {
trans start    
   some code
trans_complete
}

trans start
  some_function();
trans_complete

so when we start the transaction, some_function() also tries to run trnasaction, gets warnign,
then tries to complete, - it completes the transaction started eearlier. At this point there is no started transaction. some_function() finishes doing stuff
And then after this thre is last trans_complete call. But transaction is already finished by the some_function, so it gets warning.