Add support for SELECT FOR UPDATE

[dkopitsa]

Add clause for_update(nowait: bool = False, skip_locked: bool = False, of=()) for objects and select query.

Similar to django's select_for_update Closes #932

Example usage:

Get one row matching the status, locks it for update, and skips any already locked rows.

async with DB.transaction():
    await Outbox.objects().where(Outbox.status == 'failed').limit(1).for_update(skip_locked=True).first()

Ensure that the selected object is locked and cannot be modified by other transactions

async with DB.transaction():
     await org = Organization.objects().where(Organization.code == code).for_update()
     # do some work with object ...
     await org.save()

[sinisaos]

@dkopitsa Can you please run locally ./scripts/format.sh and then ./scripts/lint.sh to fix linter/format issue and then make another commit. After that everything should be ok because tests are passing.

[dkopitsa]

@dkopitsa Can you please run locally ./scripts/format.sh and then ./scripts/lint.sh to fix linter/format issue and then make another commit. After that everything should be ok because tests are passing.

Done. Sorry about the linters; I wanted to fix the tests quickly and used GitHub's web editor.

[codecov-commenter]

:warning: Please install the to ensure uploads and comments are reliably processed by Codecov.

Codecov Report

Attention: Patch coverage is 86.36364% with 6 lines in your changes missing coverage. Please review.

Project coverage is 88.47%. Comparing base (4ed6938) to head (fdd7f53). Report is 11 commits behind head on master.

Files	Patch %	Lines
piccolo/query/mixins.py	87.87%	4 Missing :warning:
piccolo/query/methods/objects.py	50.00%	2 Missing :warning:

:exclamation: Your organization needs to install the Codecov GitHub app to enable full functionality.

Additional details and impacted files

@@            Coverage Diff             @@
##           master    #1065      +/-   ##
==========================================
- Coverage   92.66%   88.47%   -4.20%     
==========================================
  Files         115      116       +1     
  Lines        8444     8543      +99     
==========================================
- Hits         7825     7558     -267     
- Misses        619      985     +366     

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[sinisaos]

Done. Sorry about the linters; I wanted to fix the tests quickly and used GitHub's web editor.

No worries. Thanks for your PR. Looks good to me, but you'll have to wait for @dantownsend proper review.

[deliro]

Any updates here?

[dkopitsa]

Hi @dantownsend , could you please review the PR? If there's anything that needs further work or adjustments, let me know, and I'll make the necessary changes

[dkopitsa]

Good point. Personally, I don't use anything other than 'update', but it could be useful, especially for the ORM.

await Band.select().lock_for('update', skip_locked=True)

looks good to me. I'll make the necessary changes.

[dkopitsa]

I fixed the formatting in test_select.py, please run workflow again.

[dantownsend]

@dkopitsa This looks great - thanks a lot!

I'll test it out locally.

[dantownsend]

@dkopitsa I've merged this in now, via another branch.

I just changed the method name from lock_for to lock_rows. I realised when looking at the docs that this looked a bit off when no lock strength was specified:

await Band.select().lock_for()

So now it's this instead:

await Band.select().lock_rows()

Thanks a lot for all the effort you put into this - it's a very nice feature to have 👍