SELECT FOR UPDATE with SKIP LOCKED functionality

Help Procedural SQL
1

I am trying to concurrently read a large batch of data ( ~ 200,000 rows ), enrich the data and stream it into Kafka.

Rather than read the table sequentially, I want multiple readers to simultaneously read rows.

I use the “Status” column to ensure that 2 readers do not read the same row with the SELECT FOR UPDATE.

I want to know if the SKIP LOCKED and NOWAIT functionality is supported in MemSQL similar to what is available in MySQL 8

2

MemSQL supports SELECT FOR UPDATE but not SKIP LOCKED and NOWAIT.

You should be able to achieve what you want by updating a field on the row to mark it as logically locked or logically owned for a specific consuming application process. It sounds like that is what you are doing with the “Status” column you mentioned. Alternatively, you could include filters on the query to limit consumption to exclusive ranges of data based on some other criteria.

MemSQL in-memory row store updates are very fast, so these options should be viable.

Here’s a related post: locking - MemSql > workaround for SELECT ... FOR UPDATE - Stack Overflow
from before MemSQL supported SELECT for update.