title: Postgresql Locking Rows on a Key/Foreign Key
body: <p>I'm using postgresql.  I have 3 tables.</p>
  <ul>
  <li><strong>Table A</strong> has an ID column that's a Primary Key</li>
  <li><strong>Table B</strong> and <strong>Table C</strong> have ID columns that are foreign key references to A's ID.</li>
  </ul>
  <p>In a single process, I would like to lock any rows that have a particular ID and then possibly delete rows and insert rows with that ID in B and C</p>
  <p>My current approach is</p>
  <ol>
  <li>SELECT FOR UPDATE on A on the ID.</li>
  <li>Then I try to delete and insert rows in B and C.</li>
  <li>commit/end</li>
  </ol>
  <p>Unfortunately, my code deadlocks trying to do the insert.</p>
  <p>What am I doing wrong?  What is the proper way to prevent other processes from adding, removing, or updating rows with a given ID in B and C (until I am done with my transaction)?</p>
  <p>Thanks in advance!</p>