MySQL Forums
Forum List  »  InnoDB

HOW: SELECT ...FOR...UPDATE SKIP LOCKED but allow INSERT
Posted by: Kristijan Marin
Date: January 28, 2019 03:26PM

Hi,

I have a table NOTIFICATIONS & wish to lock rows according to criteria.

If I do it this way (primary key in WHERE):
SELECT fk_message FROM i8_notificationqueue WHERE fk_message=1866 FOR UPDATE SKIP LOCKED

Then I get in DATA_LOCKS :
'i8_notificationqueue',NULL,'TABLE','IX',NULL
'i8_notificationqueue','PRIMARY','RECORD','X','1866'

and I can INSERT ...


but IF I use a nonprimary key like (I need to get all records that are in the past & we have index on it) :

SELECT fk_message FROM i8_notificationqueue WHERE dispatchdate < unix_timestamp() ORDER BY fk_message FOR UPDATE SKIP LOCKED

Then I get:
'i8_notificationqueue',NULL,'TABLE','IX',NULL
'i8_notificationqueue','PRIMARY','RECORD','X','supremum pseudo-record'
'i8_notificationqueue','PRIMARY','RECORD','X','1875'

and now I can't insert ... cause I guess 'supremum pseudo-record' locks the whole
table ...

How can I lock rows with my WHERE clause and still be able to insert new record ...

And what if I call INSERT...ON DUPLICATE KEY ... and the duplicate is found.. then I guess it will WAIT for lock to be released from the first transaction ...

What I would need is :

- IF i do INSERT and the row is not locked, then do the INSERT .. if DUPLICATE found , then UPDATE ...
- but if the row IS LOCKED then just SKIP it ... it is already in processing so no need to update it ...


This INSERT ..... ON DUPLICATE KEY is done in AFTER INSERT TRIGGER ... so when a new message is inserted, and I see that it should be sent out immediately I do an
insert into NOTIFICATIONS table



Thank you guys a lot.
Kris

Options: ReplyQuote


Subject
Views
Written By
Posted
HOW: SELECT ...FOR...UPDATE SKIP LOCKED but allow INSERT
232
January 28, 2019 03:26PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.