I am using MemSql as my DB and I need to have SELECT ... FOR UPDATE functionality. However it is not supported in 6.5 version, which I am using. Is there any workaround for this problem?
My problem is as follows: multiple processes pick a single record (that has not been process yet) from the same table, do some job out of SQL code then do UPDATE for marking the record as processed. If I had a possibility to do SELECT ... FOR UPDATE then I could lock the record for assuring that only one process can pick it.
As a workaround that I can think of is using some LockToken column and do something like
UPDATE Tbl SET LockToken = 'a_unique_token' WHERE LockToken IS NULL LIMIT 1;
SELECT * FROM Tbl WHERE LockToken = 'a_unique_token';
but in this case I get
Error Code: 1749. Feature 'UPDATE...LIMIT must be constrained to a single partition' is not supported by MemSQL Distributed.
I could also do the job with LOCK TABLES, but according to this they are not supported as well.
Is there any workaround to this type of problem?
Yes, your workaround is a good idea. One way you could workaround that error is to pick a specific row to lock instead of using LIMIT 1, like UPDATE Tbl SET LockToken = 'a_unique_token' WHERE LockToken IS NULL and id = (select id from Tbl WHERE LockToken IS NULL limit 1). (Or you could use (select min(id) from Tbl WHERE LockToken IS NULL) or something similar to pick an id depending on what you want.) This should work well if you have an index on id.
Also, you could check out version 6.7 where select for update is now supported: https://docs.memsql.com/sql-reference/v6.7/select/.