Databases
StackOverflow2013
Postsdbo
POWhen to use SELECT ... FOR UPDATE?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhen to use SELECT ... FOR UPDATE?
    primarykey
    data
    text
    <p>Please help me understand the use-case behind <code>SELECT ... FOR UPDATE</code>.</p> <p><strong>Question 1</strong>: Is the following a good example of when <code>SELECT ... FOR UPDATE</code> should be used?</p> <p>Given:</p> <ul> <li>rooms[id]</li> <li>tags[id, name]</li> <li>room_tags[room_id, tag_id] <ul> <li>room_id and tag_id are foreign keys</li> </ul></li> </ul> <p>The application wants to list all rooms and their tags, but needs to differentiate between rooms with no tags versus rooms that have been removed. If SELECT ... FOR UPDATE is not used, what could happen is:</p> <ul> <li>Initially: <ul> <li>rooms contains <code>[id = 1]</code></li> <li>tags contains <code>[id = 1, name = 'cats']</code></li> <li>room_tags contains <code>[room_id = 1, tag_id = 1]</code></li> </ul></li> <li>Thread 1: <code>SELECT id FROM rooms;</code> <ul> <li><code>returns [id = 1]</code></li> </ul></li> <li>Thread 2: <code>DELETE FROM room_tags WHERE room_id = 1;</code></li> <li>Thread 2: <code>DELETE FROM rooms WHERE id = 1;</code></li> <li>Thread 2: [commits the transaction]</li> <li>Thread 1: <code>SELECT tags.name FROM room_tags, tags WHERE room_tags.tag_id = 1 AND tags.id = room_tags.tag_id;</code> <ul> <li>returns an empty list</li> </ul></li> </ul> <p>Now Thread 1 thinks that room 1 has no tags, but in reality the room has been removed. To solve this problem, Thread 1 should <code>SELECT id FROM rooms FOR UPDATE</code>, thereby preventing Thread 2 from deleting from <code>rooms</code> until Thread 1 is done. Is that correct?</p> <p><strong>Question 2</strong>: When should one use <code>SERIALIZABLE</code> transaction isolation versus <code>READ_COMMITTED</code> with <code>SELECT ... FOR UPDATE</code>?</p> <p>Answers are expected to be portable (not database-specific). If that's not possible, please explain why.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDinidu Hewage
    1. USGili
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhen to use SELECT ... FOR UPDATE?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhen to use SELECT ... FOR UPDATE?
      1. USdreamer
      1. VTFavorite
    3. VO
      singulars
      1. POWhen to use SELECT ... FOR UPDATE?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhich RDBMS are you using?
      singulars
      1. POWhen to use SELECT ... FOR UPDATE?
      1. USQuassnoi
    2. CO@Quassnoi, as mentioned at the bottom of the question, I am looking for a portable (not database-specific) solution.
      singulars
      1. POWhen to use SELECT ... FOR UPDATE?
      1. USGili
    3. COAre the options `REPEATABLE_READ` and `READ_COMMITTED` even portable options? The only results I get for those are for MSSQL server
      singulars
      1. POWhen to use SELECT ... FOR UPDATE?
      1. USBilly ONeal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload