Transaction is not working

[eluser]kamikaz[/eluser]
Hi,

I have a very strange behaviour with oracle transaction. When this code runs, it runs absolutly not in transaction...I wonder why

Code:

$this->db->query('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
$this->db->trans_start();   //Start transact

$this->insert_owner($owner_name); //insert owner

foreach (... as ...) //Insert columns
{
     $this->insert_columns(...); //insert columns
}
$this->db->trans_commit(); //Trans commit

The two functions call "$this->db->...".

Why those statements are not in transaction ?

Thank you

[eluser]kamikaz[/eluser]
Nobody? Am I using this in a wrong way?

[eluser]jonez[/eluser]
Usually at the end you use trans_complete() not trans_commit() but I don't see why they shouldn't create the same result. Are you nesting transactions, are any other methods also calling trans_start?

[eluser]kamikaz[/eluser]
Thank you for your reply

My mistake, I use trans_complete(). I also seen in other post that "SET TRANSACTION..." goes after $this->db->trans_start(). Find that weird but it doesn't change anything.

I don't use nested transaction

When I make an insert of rows (lets say 179, it takes 3 minutes), I can see each row that are added one by one when I refresh the page.

[eluser]jonez[/eluser]
If you put a die statement after the loop and before trans_complete do they roll back? If they don't it's one of two things, either your database/table type doesn't support transactions (never used Oracle so I can't comment) or its a bug in the driver. Is the code you posted more or less complete or is it just a portion? What version of CI are you using? I'd remove the SET command unless you specifically need it.

Inserting 179 rows takes 3 full minutes? Yikes, you may want to change/remove the loop and use insert_batch. Unless there's a whole lot more going on then what you posted it should not take more then a few seconds.

[eluser]kamikaz[/eluser]
I try to put a die statement after the loop and nothing is rollbacked. None of the methods that are called use trans_start().
Oracle support transaction

It takes 3 full minutes because of lots of query I have to make.

Here is the "main" method that call everthing:

Code:

public function insert($owner_name, $values = NULL)
    {
        try
        {
            $this->load->model('auth/auth_owner');
            $this->load->model('auth/auth_column');
            $this->db->trans_start();   //Start transact

                $this->auth_owner->insert($owner_name); //insert owner
                //if multiple inserts
                if (is_array($values))
                {
                    //foreach tables
                    foreach ($values as $table_name)
                    {
                        $affected_row = $this->insert_row($owner_name, $table_name); //insert table

                        if ($affected_row === 0)
                        {
                            $this->db->trans_rollback();
                            return FALSE;
                        }

                        if ($this->auth_column->insert($owner_name, $table_name) === 0) //insert columns
                        {
                            $this->db->trans_rollback();
                            return FALSE;
                        }
                    }
                }
                else if ($values != NULL)
                {
                    $this->insert_row($owner_name, $values);
                    if ($this->auth_column->insert($owner_name, $values) === 0) //Problem inserting row
                    {
                        $this->db->trans_rollback();
                        return FALSE;
                    }
                }

            $this->db->trans_complete();
            return TRUE;
        }
        catch(Exception $e)
        {
            $this->db->trans_rollback();
            throw new Exception($e->getMessage());
        }
    }

I removed the SET command but it stays the same.

[eluser]jonez[/eluser]
Can you try a simple example? Insert a single fake record in a transaction (without committing it) and see what happens. Or throw an error and see if it rolls back. In MySQL only certain table types support transactions. Some don't, are you positive your table type supports them?

Few comments on your code;

- Remove the try/catch, a database error should (if transactions are working) throw an exception and do the rollback for you.
- Use insert_batch not loops for bulk inserts, many individual queries are a lot slower than a single one.
- Why are you rolling back 0 row inserts? Seems unnecessary, why not check the data first and only insert if you know you need to?
- Transactions use overhead. They are great way to catch errors and enforce foreign keys, but they shouldn't be used (IMO) for conditional logic. Doing so puts strain on your server that can be avoided.

[eluser]kamikaz[/eluser]
I have tried what you suggested but in this case, it didn't rollback.
So I have written a transaction with one loop to get some data and after that, two insert_batch(). The second one failed and the first one was rollbacked. Yeah!

I guess I do things that CI doesn't like.

Thank you for your relevant comments.

What do you mean by transaction shouldn't be used for conditional logic?

[eluser]jonez[/eluser]
What I mean by conditional logic is when your are working with a DB, make it do the least amount of work possible. In your case, you have a function that inserts a base record then attempts to add child rows for that record. Instead of checking if child data exists (and thus the base record is needed), you insert first, then later on you see you don't have child records and you shouldn't have inserted the base so you remove it with a rollback. This is much less efficient than checking if child rows exist before doing anything else, if not skip the entire function otherwise insert the base and add the children. Think of it as preparing the data before actually inserting it, discarding anything that isn't needed. Then use transactions to catch errors in the data (missing or invalid fields) and enforce foreign key constraints.

A good chunk of your DB commands could be avoided with data checks. PHP conditionals are considerably faster than doing insert/rollbacks. Prepare the data, use insert_batch instead of for loops for children, and you should be able to reduce the execution time significantly. 3mins for anything other than bulk data migration is too much.

As an example I recently wrote a data migration script for our system (MySQL). I had to move account data (users, files, documents, etc) from one system to another. In total it was 2058 records that were spread relationally across ~20 tables. What I did was collect everything in a series of arrays, then used transactions and bulk inserts to insert everything into the new system. Including copying their physical files (only paths are stored in DB) it took less than 30 seconds to run.

[eluser]InsiteFX[/eluser]
CodeIgniter's database abstraction allows you to use transactions with databases that support transaction-safe table types. In MySQL, you'll need to be running
InnoDB or BDB table types rather than the more common MyISAM. Most other database platforms support transactions natively.