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 172.20.0.6 openemr Updating
update sequences set id=LAST_INSERT_ID(id+1)
Trx read view will not see trx with id >= 2606781, sees < 2606773
------- TRX HAS BEEN WAITING 5952868 ns FOR THIS LOCK TO BE GRANTED:
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.
\sqlBeginTrans();
$preppedData = $this->convertArraysToRecords($pid, $contactData);
// save off our data $record is of type ContactAddress
foreach ($preppedData as $record) {
$record->persist();
}
// grab all of the NEW records and insert them in as address records for the given patient
\sqlCommitTrans();
}
catch (\Exception $exception) {
// TODO: @adunsulag handle exception
\sqlRollbackTrans();
}
class ContactAddress extends ORObjectData {
// other methods and data hidden
public function persist()
{
// first we will persist our address
if ($this->getAddress()->isObjectModified()) {
$this->getAddress()->persist();
$this->set_address_id($this->getAddress()->get_id());
}
if ($this->getContact()->isObjectModified()) {
$this->getContact()->persist();
$this->set_contact_id($this->getContact()->get_id());
}
// now we can persist with our objects saved off
return parent::persist(); // TODO: Change the autogenerated stub
}
}