Magento last insert id in middle of transaction

Magento last insert id in middle of transaction

September 26, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento last insert id in middle of transaction

Retrieving Last Insert ID in Magento Transactions

If you’re working with transactions in Magento and need to use the primary key of an inserted record within the same transaction scope, you might face challenges retrieving the last_insert_id value before committing the changes. Let’s dive into this common issue and explore a solution that ensures you can access the required ID.

The Challenge: Accessing Last Insert ID Before Commit

When using transactions in Magento, you typically follow a pattern where you add objects to the transaction and then call $transaction->save() to execute all queries within a single database connection. However, retrieving the last_insert_id value immediately after adding an object to the transaction might not yield the expected result:

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$lastInsertId = $connection->fetchOne('SELECT last_insert_id()'); // Returns 0 or incorrect value

This happens because the last_insert_id function returns the value of the last insert query executed within the current session, but in a transaction context, the actual insertion might not have occurred yet.

The Solution: Waiting for Insertion After Adding to Transaction

To retrieve the correct last_insert_id, you need to ensure that the insertion has actually taken place before attempting to fetch the ID. Here’s how you can achieve this:

  1. Add your objects to the transaction as usual:
$model1->setField1()
       ->setField2();
$transaction->addObject($model1, 'insert');
  1. After adding the object, manually execute the corresponding insert query using the resource model or a direct database connection:
$resource = Mage::getSingleton('core/resource');
$writeConnection = $resource->getConnection('core_write');

$query = "INSERT INTO my_table (field1, field2) VALUES ('value1', 'value2')";
$writeConnection->query($query);
  1. Retrieve the last_insert_id value immediately after executing your custom query:
$lastInsertId = $resource->getConnection('core_read')->fetchOne('SELECT last_insert_id()');
  1. Now you can use this $lastInsertId to set values for subsequent objects in the transaction:
$model2->setField3($lastInsertId);
$model3->setField4($lastInsertId);

$transaction->addObject($model2, 'insert');
$transaction->addObject($model3, 'insert');

$transaction->save();

Best Practices and Considerations

When dealing with last insert IDs in Magento transactions:

  • Always ensure you have a direct database connection for executing custom queries.
  • Be cautious about mixing transaction methods (e.g., save(), $connection->query()) to avoid conflicts or unexpected behavior.
  • Optimize your code by minimizing the number of direct database interactions within transactions.

Conclusion

Retrieving the last insert ID before committing a Magento transaction requires careful handling of query execution order. By manually executing the insertion query after adding objects to the transaction and before fetching the ID, you can ensure that you obtain the correct value needed for subsequent record creation.

Remember, proper resource management is crucial when working with transactions in Magento. Always ensure you have the right connection type (core_read or core_write) to prevent data inconsistencies or performance issues.

For more advanced techniques and best practices in Magento development, be sure to explore our comprehensive guides and resources at Laravel Company.

Database Errors

Our network