Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Saving on `afterCreate()` causes model.zep to throw Exception on `refresh()`

Hello,

I'm doing an intervention on my application regarding the way to generate a unique and sequential Order Number.

At the moment the Order Number is generated on beforeValidationOnCreate(), where a query is performed on the table looking for the latest record and setting as order number the same retreived value + 1.

I've changed this behaviour because concurrent transactions sometimes cause two records being created with the same order number (the "second" order causes database exception for unique index violation, that is a correct behaviour). On the other hand, using forUpdate() or sharedLock() during the order creation transaction to retreive the latest order number causes this error: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction.

So, I've decided to do this:

  1. OrderNumber field becomes DEFAULT NULL with a UNIQUE index
  2. On afterCreate(), I copy the value of OrderId into OrderNumber and save the model: $this->save()

The problem is that if I perform a $order->refresh() at some point after it was created/saved this way (and I do the refresh() on the same instance of the created model), I get this error:

Phalcon\Mvc\Model\Exception

The record cannot be refreshed because it does not exist or is deleted

File: phalcon/mvc/model.zep
Line: 3433

$order dirty state is PERSISTENT when this happens.

I'm sure the problem is that I save() a model on afterCreate(), causing the model to be no more a "creation" and making it become an "update".

How can I fix this problem?

Thank you very much

Using PHP 7.0 with Phalcon 3.2.0 on Debian 8 with MySql 5.7

Why not just use an AUTO_INCREMENT column and let the database worry about consistency?



6.7k

Why not just use an AUTO_INCREMENT column and let the database worry about consistency?

I already have an AUTO_INCREMENT column which is the table ID column. Anyway that wasn't my question. The point is: is saving (updating) a record in afterCreate permitted? How can I prevent that error?

I don't know where I read it, but I believe you can't call save() in afterCreate(). I have no source for that info, and it still shouldn't be causing an exception like that, but I don't think that's allowed.

My suggestion of AUTO_INCREMENT was an attempt to potentially solve your root problem rather than the symptom. Oftentimes people (myself included) get so wrapped up in fixing a technical problem that we can't step back and see the bigger issue. You say you're copying orderID into orderNumber. That's the same data in 2 columns, and you're looking for a solution where they're guaranteed to be the same. To me it seems pretty clear the cleanest solution is to change your thinking a bit, get rid of the orderNumber column and just use the orderID, since they're going to be the same value anyway



6.7k
Accepted
answer

I don't know where I read it, but I believe you can't call save() in afterCreate(). I have no source for that info, and it still shouldn't be causing an exception like that, but I don't think that's allowed.

My suggestion of AUTO_INCREMENT was an attempt to potentially solve your root problem rather than the symptom. Oftentimes people (myself included) get so wrapped up in fixing a technical problem that we can't step back and see the bigger issue. You say you're copying orderID into orderNumber. That's the same data in 2 columns, and you're looking for a solution where they're guaranteed to be the same. To me it seems pretty clear the cleanest solution is to change your thinking a bit, get rid of the orderNumber column and just use the orderID, since they're going to be the same value anyway

Yes, I know... Thank you for your suggestion, anyway I need the OrderNumber column to stay where it is for backward compatibility and most importantly because I wanted to generate an OrderNumber based on the ID (ie. a base16 of the ID or something like that...).

I think you're right: no "self save" inside afterCreate is permitted. It's like native TRIGGERs in SQL: on AFTER INSERT you can't update NEW record.

So I think the solution would be to perform a raw query inside afterCreate, tricking the Model to let him think that no updates were made.

public function afterCreate() {
    $this->setOrderNumber( SomeClass::generateOrderNumber( $this->getId() ) );

    $db = $this->getWriteConnection();

    $db->query( 'UPDATE the_order_table SET the_order_number = :order_number WHERE the_order_table_id = :purchase_order_id ', [
        ':order_number'         => $this->getOrderNumber(),
        ':purchase_order_id'    => $this->getId(),
    ]);
}