Saya memiliki sepotong kode PHP yang tidak dapat menjalankan kueri lagi karena kunci tertunda atau transaksi sudah terbuka di DB MySql yang masih belum saya temukan. Script diluncurkan oleh cron setiap jam. Kode yang melanggar adalah ini
CommandHelper::log("Calling beginTransaction for item $item->id");
$transaction = Yii::app()->db->beginTransaction();
CommandHelper::log("Trying to get update lock for item $item->id");
try {
// this line will lock a single row
$item = Item::model()->findBySql("SELECT * FROM " . $item::model()->getTableSchema()->name . " WHERE id=$item->id FOR UPDATE");
if(!$item)
throw new Exception("Item $item->id not found in processItem");
CommandHelper::log("Obtained update lock for item $item->id");
$now = DateTimeHelper::getInstance()->getStringNow();
// the following call executes a few queries and causes an exception
$this->processExpiredDeferredPayments($item,$now);
// we won't get here
CommandHelper::log("Item $item->id query confirmed units");
....
....
$transaction->commit();
} catch (Exception $e) {
$transaction->rollBack();
CommandHelper::log("Transaction error:" . $e->getMessage());
}
Log mencetak baris-baris ini setiap jam:
...
...
2017-06-03 14:46:04 Item 164 start processing
2017-06-03 14:46:04 Calling beginTransaction for item 164
2017-06-03 14:46:04 Trying to get update lock for item 164
2017-06-03 14:46:04 Obtained update lock for item 164
2017-06-03 14:46:04 Transaction error:There is already an active transaction
2017-06-03 14:46:04 Item 164 end processing
2017-06-03 14:46:04 Item 160 start processing
2017-06-03 14:46:04 Calling beginTransaction for item 160
2017-06-03 14:46:04 Trying to get update lock for item 160
2017-06-03 14:46:04 Obtained update lock for item 160
2017-06-03 14:46:04 Transaction error:There is already an active transaction
2017-06-03 14:46:04 Item 160 end processing
2017-06-03 14:46:04 Item 182 start processing
2017-06-03 14:46:04 Calling beginTransaction for item 182
2017-06-03 14:46:04 Trying to get update lock for item 182
2017-06-03 14:46:04 Obtained update lock for item 182
2017-06-03 14:46:04 Transaction error:There is already an active transaction
2017-06-03 14:46:04 Item 182 end processing
...
...
Ini adalah keluaran dari show engine innodb status
=====================================
2017-06-03 15:00:16 2af55a655700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 139314 srv_active, 0 srv_shutdown, 2478167 srv_idle
srv_master_thread log flush and writes: 2617481
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 139170
OS WAIT ARRAY INFO: signal count 139073
Mutex spin waits 15233, rounds 456961, OS waits 14580
RW-shared spins 123701, rounds 3711390, OS waits 123173
RW-excl spins 89, rounds 42528, OS waits 1410
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 477.84 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 233590159
Purge done for trx's n:o < 233589999 undo n:o < 0 state: running but idle
History list length 2636
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 225441219, not started
MySQL thread id 271018, OS thread handle 0x2af55a655700, query id 28702104 172.31.10.242 ebroot init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 271017, OS thread handle 0x2af55bfa6700, query id 28698794 172.31.10.242 ebroot cleaning up
---TRANSACTION 233590158, not started
MySQL thread id 1, OS thread handle 0x2af55a550700, query id 28702102 localhost 127.0.0.1 rdsadmin cleaning up
--------
FILE I/O
--------
...
...
Dan ini adalah keluaran dari show full processlist:
Transaksi atau kunci mana yang tertunda di sini dan bagaimana cara mematikannya?
Dan mengapa tidak ditutup secara otomatis ketika skrip PHP berakhir? Saya pikir meskipun utas PHP memiliki koneksi Mysql, ketika utas keluar, koneksi MySQL ditutup secara otomatis dan transaksi/kunci yang dimulai olehnya harus dikomit atau dibatalkan secara otomatis, bukan? Terima kasih
