Updating sequences table inside transaction for multiple objects causes table locking

Just thought I’d throw this out there for the benefit of anyone else that may be running into this issue.

I’ve got three objects that all inherit from the ORDataObject class. Address, Contact, and ContactAddress. I need to update all three of these objects so I roll them into a transaction and then update them.

However, since these are three new objects the ORDataObject calls the OpenEMR generate_id() function. This function uses the sequences table to get a globally unique database identifier for OpenEMR for each object. It uses the AdoDB GenID function on the sequences table. This in turn executes the following query: update sequences set id=LAST_INSERT_ID(id+1).

When I try to update these three objects I end up getting database transaction timeouts and exceptions thrown due to a lock contention on the sequences table. When I drill down into the InnoDB engine using:

show engine innodb status;

I see that I’m getting thread contentions on the sequences table. One thread has locked the table due to the first update and another thread is trying to acquire the lock so it can also do the sequences update.

---TRANSACTION 2606781, ACTIVE 66 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MariaDB thread id 76, OS thread handle 140579447576320, query id 55443 openemr Updating
update sequences set id=LAST_INSERT_ID(id+1)
Trx read view will not see trx with id >= 2606781, sees < 2606773
RECORD LOCKS space id 31528 page no 3 n bits 8 index GEN_CLUST_INDEX of table `openemr`.`sequences` trx id 2606781 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000011543; asc      C;;
 1: len 6; hex 00000027c6b5; asc    '  ;;
 2: len 7; hex 430000091b0edf; asc C      ;;
 3: len 4; hex 00000341; asc    A;;


I’m not sure why AdoDB is not re-using the same thread connection when we are working inside of a transaction. That’s something that is usually fairly typical of other Database Abstraction Libraries (DAL) I’m familiar with. It should use the connection pool when we are operating outside of a transaction, but when we are inside of a transaction all queries should re-use the same MySQL connection thread to avoid table contention like I am experiencing.

What I’ll have to do to get through this problem until we can figure out why AdoDB is not handling the transaction threading properly is to just drop putting my code in a db transaction.

Relevant sample code is the following:

 try {
            // wrap the entire thing in a transaction so we are idempotent.

            $preppedData = $this->convertArraysToRecords($pid, $contactData);

            // save off our data $record is of type ContactAddress
            foreach ($preppedData as $record) {

            // grab all of the NEW records and insert them in as address records for the given patient
        catch (\Exception $exception) {
            // TODO: @adunsulag handle exception
class ContactAddress extends ORObjectData {
// other methods and data hidden
public function persist()
        // first we will persist our address
        if ($this->getAddress()->isObjectModified()) {
        if ($this->getContact()->isObjectModified()) {

        // now we can persist with our objects saved off
        return parent::persist(); // TODO: Change the autogenerated stub

It is malpractice to use existing generate_id when we have several other tables that use auto_increment. You can review #4547 and related PR#4862 where we remove auto_generate fields before persist and then use set method using last inserted key returned by adodb.

Unless one really has complex column get/set methods, adodb native methods as used in that same PR do the same abstraction. So increasingly we are convinced ordataobject is extra layer without providing much value.