לוחות מודעות לוחות מודעות

Bugs

Fixing “Lock wait timeout exceeded; try restarting transaction” for MySQL

מתג
Liferay 7 CE GA3 uses not-necessarily-applicable versions of MySQL/MariaDB JDBC drivers (probably due to licensing restrictions of MariaDB/MySQL).

I trieid to download proper version of MySQL JDBC driver directly, and remove Liferay provided drivers (remove both: MySQL and MariaDemoticon.

But it didn't work. Liferay locks table/record, and I cannot delete record directly from separate MySQL from console. When I stop Liferay instance, I can delete record.

This is a bug in Liferay.
0 (0 הצבעות)

RE: Fixing “Lock wait timeout exceeded; try restarting transaction” for MyS
תשובה
14:42 11/09/16 כתגובה ל-Fuad Efendi
I was trying to delete 30,000 records in a loop, and it seems that MySQL has *more than a few minutes delay* somewhere in the middle of the loop. I was following BlogsEntry example of deletion all assets by Group ID.

Try to use high numbers if you encounter such problem:

SET GLOBAL innodb_lock_wait_timeout = 600000;
SET innodb_lock_wait_timeout = 600000;
0 (0 הצבעות)

RE: Fixing “Lock wait timeout exceeded; try restarting transaction” for MyS
תשובה
15:29 11/09/16 כתגובה ל-Fuad Efendi
I followed this pattern:

1234567

    @Override
    public void deleteEntries(long groupId) throws PortalException {
        for (BlogsEntry entry : blogsEntryPersistence.findByGroupId(groupId)) {
            blogsEntryLocalService.deleteEntry(entry);
        }
    }


However, in my special case I had 130,000 records, and it became huge transaction so that even setting timeout to 10 minutes causes hanging application (just after few thousands deletes). I believe this is because of Hibernate transaction wrapper.


So that I moved this logic outside of service layer, and now I have smaller thansactions, and it works.

1234567891011

List<CJProduct> entries = null;
        entries = _cjProductLocalService.findByGroupId(groupId, 0, 1000);

        while (entries != null && entries.size()>0) {

            for (CJProduct entry : entries) {
                _cjProductLocalService.deleteEntry(entry);
            }
            entries = _cjProductLocalService.findByGroupId(groupId, 0, 1000);
        }


Note that a code snippet above is OUTSIDE of *ServiceImpl; otherwise it will bececome huge transaction.
0 (0 הצבעות)