fikurimax's avatar

Error data partial insertion even using DB::transaction

The case is that I want to insert data into 2 tables using a transaction. As far as I know, transactions in the database ensure data consistency. However, in my case, the data in table 1 didn't go in, but the data in table 2 did. I checked the query logs those 2 query is there. But when I get the Table1 data inside the job, the data is not exists and so does in the database, and there was no 'delete from' query in the logs. So, I assume this might be related to the table during the transaction that could possibly resulting in partial insertion. Are there any other factors that could cause this case?

Also, this happen occasionally, not like everytime. But lately this happened quite frequent.

Here is my controller code:

  try {
    DB::beginTransaction();
    Table1::create(...);
    Table2::create(...);
    DB::commit();

    dispatch(...);
  } catch($th) {
    DB::rollBack();
  }

And here is the queue code:

public function handle(){
    $data = Table1::find($this->id_table1);
    if ($data === null) {
        // data Table1 doesn't exist
        // data Table2 exists
    }
  }
0 likes
7 replies
fikurimax's avatar

The problem still occur, in case anyone has an answer I am really forward to it. Thanks

Snapey's avatar

or, the problem is really that you don't retrieve the data correctly later

fikurimax's avatar

@Snapey Thanks for your reply!. The code mostly works, my main problem is the data on Table2 is exists but not for Table1.

I dont know If this is the problem or not, the Table1 id is generated manually using this

function gen_transaction_new_version(string $identity = '40')
{
        $suffixID           = md5(microtime());
        $getCurrDate        = date('md');

        return $identity . $getCurrDate . substr($suffixID, -9);
}

I assume if the Id is not unique it should throw an exception and the transaction would be rolled back

Snapey's avatar

@fikurimax You could clear both tables then run your insert multiple times then simply count the rows?

obviously your key generation is not guaranteed unique, since two records could be in the same microsecond

fikurimax's avatar

@Snapey that's worth to try, I'll try it later thanks. Btw, I use Lumen 8, not Laravel. And I read somewhere on SO someone has quite similar problem with get() or first(). Which they can't get data using Eloquent but they can with query builder. So maybe I could investigate more if there are differences between them?

Also yeah the key generation is not guaranteed unique, but we don't have a problem if the key is not unique, as long as the transaction fail. But so far, because the Table2 also has the Table1 key, when I look for the Table1 data with that id there always none. So, there's no key collision so far.

Please or to participate in this conversation.