Based on this FAQ, I would like to ask how I can prevent another transaction to insert a row in a table with a PK value I'm about to insert?
To use the sample from the other question, say I have this table:
CREATE TABLE MyTermTable ( MyTerm INTEGER NOT NULL PRIMARY KEY, counter INTEGER NOT NULL );
And I have a particular value for PK column MyTerm to insert (say, as @MyTerm).
How can I prevent others to insert that value?
Current solutions (from the cited FAQ) include:
Are there other solutions that allow "my insert" to succeed and allow other transactions to get as much work done as possible?
Note: This is primarily a question to get a better understanding how to solve such generic problems.