HOW: SELECT ...FOR...UPDATE SKIP LOCKED but allow INSERT

[Kristijan Marin]

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