ORM: Skip Locked

[ferbguy]

I was working with the ORM and have multiple parallel processes picking up records from the same table. I needed to skip the currently locked rows for every new process. MySQL provides 'skip locked' method, does EspoCRM have an equivalent?
Or is there any other way I can achieve this behaviour?

[yuri]

Not supported, as it's a new feature of MySQL is I know..

[ferbguy]

Thank you, Yuri!
How else could I go about it?

[yuri]

Well, MySQL didn't have this feature until recently and millions of applications was getting away without it.

I'd figure out why rows is being locked. You didn't provide enough information about what exactly is going on. Do you have dead locks?

[ferbguy]

I have a table with many records each consisting information required to complete a task. I have a job which picks up 10 records each time it executes, multiples of this job can be triggered and can run in parallel. So, when multiple of the jobs are triggered I don't want them to pick up the same records. Hence, the requirement for a skip-locked.

[yuri]

Such scenarios existed long before we had MySQL 8.0.1. This is not Espo related question, it'd rather search how to deal with it on StackOverflow.

Possible solution:

Add a boolean field "isLocked". Set to to true before processing, set is to false after processing. In a select query use a filter "isLocked is false". You can also add an index if any the query is performing slow.

When you setting isLocked to true or to false, use transaction to avoid other job doing the same in the same moment.

[yuri]

As we support MySQL 5.7 and likely will be supporting for long in the future, we can't implement new MySQL features in ORM.