Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None

    Description

      As being discussed in MDEV-13115. It would be great if the UPDATE syntax would allow for the SKIP LOCKED instruction similar on how it's implemented for SELECT * FROM x FOR UPDATE SKIP LOCKED.
      For example:

      UPDATE tableX SET col1=1 SKIP LOCKED

      Which would update all records in tableX which rows aren't currently locked.

      Attachments

        Issue Links

          Activity

            danblack Daniel Black added a comment -

            Thanks for the use case in MDEV-13115.

            I've had a bit of a look at Oracle, Postgres, SQLite and have yet to see this supported feature on any UPDATE sql syntax. Can you see anything? Anything similar in other syntaxes like MERGE/UPSERT?

            Sqlite has https://www.sqlite.org/lang_update.html which has a on-conflict ignore (https://www.sqlite.org/lang_conflict.html) which I don't think is the same thing because AFAIK sqlite is table level locks.

            danblack Daniel Black added a comment - Thanks for the use case in MDEV-13115 . I've had a bit of a look at Oracle, Postgres, SQLite and have yet to see this supported feature on any UPDATE sql syntax. Can you see anything? Anything similar in other syntaxes like MERGE/UPSERT? Sqlite has https://www.sqlite.org/lang_update.html which has a on-conflict ignore ( https://www.sqlite.org/lang_conflict.html ) which I don't think is the same thing because AFAIK sqlite is table level locks.

            I've unfortunately not found another database implementing this, while the adaptation of the SELECT ... FOR UPDATE SKIP LOCKED syntax seems to be pretty wide. I did find however that MS-SQL implements a table hint: READPAST (https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15) which seems to achieve the same goal.
            For me it seems also just a logical extension of the SELECT ... FOR UPDATE SKIP LOCKED syntax, to be support on UPDATE and possibly DELETE (not required for my specific use-case). Since we already crossed the bridge of none-repeatable reads introduced by this syntax, why not support none-repeatable/deterministic update's(/delete's). I understand this violates many of the core foundations of a ACID database. But it seems to be very use-full in certain use-cases. And i do think when you know what your doing this allows for great features build upon.

            olafbuitelaar Olaf Buitelaar added a comment - I've unfortunately not found another database implementing this, while the adaptation of the SELECT ... FOR UPDATE SKIP LOCKED syntax seems to be pretty wide. I did find however that MS-SQL implements a table hint: READPAST ( https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15 ) which seems to achieve the same goal. For me it seems also just a logical extension of the SELECT ... FOR UPDATE SKIP LOCKED syntax, to be support on UPDATE and possibly DELETE (not required for my specific use-case). Since we already crossed the bridge of none-repeatable reads introduced by this syntax, why not support none-repeatable/deterministic update's(/delete's). I understand this violates many of the core foundations of a ACID database. But it seems to be very use-full in certain use-cases. And i do think when you know what your doing this allows for great features build upon.
            danblack Daniel Black added a comment -

            Thanks for looking. It wasn't a prerequisite, just something to syntax model if it did exist.

            Significant aspects of SELECT ... FOR UPDATE SKIP LOCKED are going to be reusable with this feature.

            danblack Daniel Black added a comment - Thanks for looking. It wasn't a prerequisite, just something to syntax model if it did exist. Significant aspects of SELECT ... FOR UPDATE SKIP LOCKED are going to be reusable with this feature.
            danblack Daniel Black added a comment -

            monty, serg do you have a syntax preference or objection to this feature?

            danblack Daniel Black added a comment - monty , serg do you have a syntax preference or objection to this feature?

            what are the use cases?

            serg Sergei Golubchik added a comment - what are the use cases?
            olafbuitelaar Olaf Buitelaar added a comment - see; https://jira.mariadb.org/browse/MDEV-13115?focusedCommentId=184792&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-184792 But generally i would say certain kind of queue's with concurrent workload.
            PavelCibulka Pavel Cibulka added a comment -

            DELETE SKIP LOCKED - would be nice for cleaning old rows. (Remove all rows not modified in last year etc)

            PavelCibulka Pavel Cibulka added a comment - DELETE SKIP LOCKED - would be nice for cleaning old rows. (Remove all rows not modified in last year etc)

            People

              Unassigned Unassigned
              olafbuitelaar Olaf Buitelaar
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.