writeEdit event - Database locked when Update data before Editor re-read it

LoveCode

For my Laravel 5.5.19 (PHP 7.1 and MySql 5.5.40) application developed with DataTables-Editor 1.7.4, I found the fantastic writeEdit event ("Data has been written to the database, but not yet read back, allowing the database to be updated before the data is gathered to display in the table"),
but when, in my writeEdit function, I try to execute an UPDATE to the same row I catch an SQL Exception :

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: update wp_assegnazione_lotti set importo_da_fatturare = 900 where (id = rcaboni-100-110))

The writeEditor is very simple:

public function preUpdateOffer($editor, $id, $values) {
    DB::table('wp_assegnazione_lotti')->where([['id', '=', $id]])
            ->update(['importo_da_fatturare' => '900']);

}

it seems we are still in transaction with the editor update and we cannot update the row.
The Database connection for editor is created for it and we not reuse the database connection used by Laravel.

Am I doing samething wrong ? or there is a bug ?****

allan

The problem is that Editor has a write lock on the table since it is in the middle of a transaction. You could use ->transaction(false) to solve that, but if you want to keep transactions enabled you'd need to use the Editor database connection, since that is what has the write lock. $editor->db() will give you that. The reference docs are available here.

Allan

LoveCode

Thanks allan; with $editor->db() now the UPDATe works fine.

LoveCode

An other question on the same case.

If in writeEdit I change on database other fields (further the field in editing) there is a way to request to Datatables refresh the whole rows and not only the field in editing ?
Sample :
Datatables with field1,field2,field3
The user edit field1 and the writeEdit update field2, but datatables show old field2 value.

Thanks.