Stack Overflow
Stack Overflow

Questions tagged [select-for-update]

Use this tag for questions about the SQL construct SELECT ... FOR UPDATE, which is an extension of standard SQL found in some databases to lock result rows as soon as you fetch them. This strategy is known as pessimistic locking.

References:

Related tags:

65 questions
168
votes
3 answers

When to use SELECT ... FOR UPDATE?

Please help me understand the use-case behind SELECT ... FOR UPDATE. Question 1: Is the following a good example of when SELECT ... FOR UPDATE should be used? Given: rooms[id] tags[id, name] room_tags[room_id, tag_id] room_id and tag_id are…
Gili
  • 86,244
  • 97
  • 390
  • 689
66
votes
1 answer

Postgres SELECT ... FOR UPDATE in functions

I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function: Does it matter which columns I select? Do they have any relation to what data I need to lock and then update? SELECT * FROM table WHERE x=y FOR…
Dan Taylor
  • 677
  • 1
  • 5
  • 6
49
votes
3 answers

SQLAlchemy - select for update example

I'm looking for a complete example of using select for update in SQLAlchemy, but haven't found one googling. I need to lock a single row and update a column, the following code doesn't work (blocks forever): s =…
user317033
42
votes
3 answers

How to use "select_for_update()" to get an object in Django?

As the Django Documentation says, select_for_update returns a Queryset. But get does not. Now I have a query which I am sure is going to return only one tuple. But I also need to acquire locks for this transaction. So I am doing something like: ob =…
Indradhanush Gupta
  • 4,067
  • 10
  • 44
  • 60
28
votes
2 answers

Why use SELECT FOR UPDATE? (MySQL)

I have a question regarding what purpose we are using SELECT FOR UDPATE? What does it do exactly? I have 2 tables, from that I need to select rows from table and update the same rows. For example: Select Query SELECT * from t1 WHERE city_id=2 for…
Duke
  • 35,420
  • 13
  • 53
  • 70
20
votes
4 answers

Django select_for_update cannot be used outside of a transaction

I was using Django 1.5.1 and upgraded to Django 1.6.6. In Django 1.5.1, I was using select for update to guarantee atomic execution. # "views.py" from django.db import transaction def some_method(): job_qs =…
Kingpin2k
  • 47,277
  • 10
  • 78
  • 96
18
votes
5 answers

Why can't I use SELECT ... FOR UPDATE with aggregate functions?

I have an application where I find a Sum() of a database column for a set of records and later use that sum in a separate query, similar to the following (made up tables, but the idea is the same): SELECT Sum(cost) INTO v_cost_total FROM…
BimmerM3
  • 349
  • 1
  • 3
  • 12
10
votes
5 answers

What's the purpose of SELECT ... *FOR UPDATE*?

I'm confused as to why you would specify FOR UPDATE -- why does the database care what you're going to do with the data from the SELECT? EDIT: Sorry, I asked the question poorly. I know the docs say that it turns things into a "locking read" -- what…
Billy ONeal
  • 104,103
  • 58
  • 317
  • 552
8
votes
1 answer

Postgres: SELECT FOR UPDATE does not see new rows after lock release

Trying to support PostgreSQL DB in my application, found this strange behaviour. Preparation: CREATE TABLE test(id INTEGER, flag BOOLEAN); INSERT INTO test(id, flag) VALUES (1, true); Assume two concurrent transactions (Autocommit=false,…
Ivan Fedenkov
  • 133
  • 1
  • 6
5
votes
2 answers

Why doesn't "select_for_update()" work in Django with MySQL?

I have a table like below: SELECT id, name FROM node; +----+------+ | id | name | +----+------+ | 5 | na | +----+------+ Then define below function: >>> def foo_with_sfu(seconds): ... with transaction.atomic(): ... node =…
Kramer Li
  • 2,284
  • 5
  • 27
  • 55
5
votes
2 answers

"FOR UPDATE is not allowed with aggregate functions" in PostgreSQL

Here is pseudo code for what I'm trying to do: rate_count = SELECT COUNT(id) FROM job WHERE last_processed_at >= ? current_limit = rate_limit - rate_count if current_limit > 0 UPDATE job SET state='processing' WHERE id IN( SELECT id FROM…
Farzher
  • 13,934
  • 21
  • 69
  • 100
4
votes
1 answer

postgres: update a row on conflict insert and return old values

I need a query to update a row in table, but if the id doesn't exist it inserts default values. Also it has to avoid threads race conditions. I found an answer here that should be fine https://stackoverflow.com/a/7927957/8372336 Using this…
91DarioDev
  • 1,612
  • 1
  • 14
  • 31
4
votes
1 answer

Why row is visible to several sessions when selected FOR UPDATE SKIP LOCKED?

Assume there are two tables TST_SAMPLE (10000 rows) and TST_SAMPLE_STATUS (empty). I want to iterate over each record in TST_SAMPLE and add exactly one record to TST_SAMPLE_STATUS accordingly. In a single thread that would be simply this: begin …
diziaq
  • 6,881
  • 16
  • 54
  • 96
4
votes
1 answer

select "for update" with JDBC?

I want to create a for update select statement in Java using JDBC, but not sure how it would be done. If you are unfamiliar with for update you can read about it…
Arya
  • 8,473
  • 27
  • 105
  • 175
3
votes
0 answers

Postgres SubQuery Limit For Update not respecting Limit

I recently upgraded my postgres db from 9.5.4 to 10.7 and noticed some odd behavior with an existing query. The trimmed down version looks like this: update mytable set job_id = 6 where id in ( select * from ( select id…
Ian Burns
  • 31
  • 2
1
2 3 4 5