0

I have a statement like this and it works fine, but there is a warning:

 update addresses_not_synced
    SET lock_until =DATE_ADD(now(), interval 60 second)
    where (lock_until < now()
        or lock_until is null)
      and (SELECT @selectedAddressesForSync :=
                          CONCAT_WS(',', concat(id, "_", address),
                                    @selectedAddressesForSync))
    limit 5;

and the warning is :

Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

mysql doesn't accept none user variable in this position. Is there any suggestion for this?

Hamid Naghipour
  • 3,465
  • 2
  • 26
  • 55
  • Adding sample data would greatly help your question. – Tim Biegeleisen Nov 22 '21 at 06:26
  • @TimBiegeleisen There is no need for data! this is a simple update statement that can apply to any data. That's not my question about data! – Hamid Naghipour Nov 22 '21 at 06:28
  • Provide the task itself, not only the way which you should to apply for this task solving. Now your solution looks like an attempt to combine UPDATE and SELECT, and it looks illogically due to ORDER BY absence. – Akina Nov 22 '21 at 06:30
  • @Akina It is quite clear :) I want to update 5 rows and also know witch rows updated. with this statement it works and I'm looking a better solution. – Hamid Naghipour Nov 22 '21 at 06:33
  • What is precise MySQL version? Does `addresses_not_synced` contains PK column/expression (for example, autoincremented column) which allows the row to be identified uniquely? PS. I'd recommend you to use SP, not this trick in single query. – Akina Nov 22 '21 at 06:41
  • @Akina current latest version. Yes it does. Id is an auto increment column. This is a query of an SP. – Hamid Naghipour Nov 22 '21 at 06:51
  • 1
    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fbebc85347c1032ab483503fea07d00f – Akina Nov 22 '21 at 07:35
  • @Akina You have used @ output := that is a user variable and I'm looking a way to avoid of this type of variable. – Hamid Naghipour Nov 22 '21 at 09:25
  • In MySQL there is no possibility for to combine data update and some output in one query except user-defined variable assigning. Perform your action in stored procedure - this allows to use one CALL and at the same time to perform a lot of actions. – Akina Nov 22 '21 at 09:27
  • @Akina Yes you're right. We can use S.P but inside a SP you have to select updatable rows for update, insert them in a temp table and then update them. So you have all updated ids in temp table. But if you have a system with high concurrency, the select for update result is a read lock and update is a right lock. So you will see more deadlock! – Hamid Naghipour Nov 22 '21 at 09:41
  • Lock tables before the actions and release after committing. Lock may fail due to concurrent processes but cannot result in deadlock. Or use SELECT .. FOR UPDATE for implicit locking - but your query must be deterministic in this case, i.e. ORDER BY needed. – Akina Nov 22 '21 at 09:47
  • @Akina In our system as a trading platform, we can't lock a table. I have used from update because for this, only update on a table. update without select we never see any deadlock. Use of user variable in update is works fine and only my issue is this: user variables within expressions is deprecated and will be removed And I don't know how can I save this query. – Hamid Naghipour Nov 22 '21 at 09:58
  • 1
    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=48844444551f8e3c140ebd7d9cadd488 – Akina Nov 22 '21 at 10:05
  • @Akina Nice move. If we have a Node.js app that call this procedure, and it calls 5 time per second, so this procedure called 5 times in concurrency. one row can select in more than one call. Also your code has select and update together in a S.P and my code has only one update :( why mysql wants to remove user variable whyyyy :( Thanks for your code anyway. – Hamid Naghipour Nov 22 '21 at 10:13
  • Lock table read and write before INSERT..SELECT and release after UPDATE. None concurrent process will interfere (but may fail). – Akina Nov 22 '21 at 10:25
  • @Akina OK thanks for your time. I hope I find a way that no need for select and lock, just like this update solution. If I can't I will use select and lock way. Thanks again. – Hamid Naghipour Nov 22 '21 at 10:52

0 Answers0