How to prevent another transaction from inserting the row I'm about to insert?

[VolkerBarth]

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:

• Just try to insert that row and handle according PK conflicts accordingly.

• Do a blocking select (with requires isolation level 3) and insert the row. PK violations aren't possible. Disadvantage: This may prevent insertions of any different @MyTerm value as well, and deadlocks need to be handled.
  (Note: The amount of blocking will depend on what kind of table/index scan is used to build the select's result set. As I understand, when the query optimizer uses an index (e.g. for the MyTerm PK), lesser rows will be blocked.)

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.

[VolkerBarth]

If there are any more insights, dear experts, I'd really like to hear them:)

[Former Member]

Please describe your situation more detailed. Usually asking general questions means getting general answers like "learn documentation about transaction isolation levels". If you need select data while it's updated then you can use snapshot isolation level or dirty read for example.

[VolkerBarth]

...means getting general answers..."

I see and appreciate your point, and I have the impression that more general (or "theoretical") questions tend to get generally less anwers here - possibly as we do not tend to respond simply with "learn documentation" in this forum:)

Besides that, I don't think I can offer much more details. It's all about INSERTs, not UPDATEs, so I guess snapshot isolation won't affect the behaviour (Note: I assume no other transaction would try to change an already existing PK to the value I'm about to insert as PK).

[Former Member]

I f tou app trying to insert row with the same PK and the new row don't differ drom old row than may be will be better to replace "Do blocking select" by INSERT INTO [ owner.]table-name [ ( column-name, ... ) ] [ ON EXISTING SKIP clause? But if new row with tha same PK differ from old row, will be better use ON EXISTING UPDATE clause or even declare MyTerm INTEGER NOT NULL PRIMARY KEY default autoincrement? Sorry, if I am still don't understand your question and my answer is bad.

[VolkerBarth]

Thanks for your hints, Alexey.

I've risen this question particularly because as Glenn has told in a comment on this other FAQ, INSERT ON EXISTING itself does not guarantee that no other transaction might try to insert the same row, i.e. the check whether a row with that PK already exists might be executed by a second transaction before the first one has inserted its data, and still a PK violation might arise. (That has come as a surprise to me!)

Possibly this is all to theoretical. In practise, I have always used DEFAULT (GLOBAL) AUTOINCREMENT PKs if applicable, and had never had such concurrency issues:)

[MCMartin]

What about the Lock Table Statement? You have control independent of the current isolation level.

[VolkerBarth]

Sure, but that would also prevent any modification on that table - including all updates and deletes of existing rows...

[MCMartin]

So performance is the key of your question.

[VolkerBarth]

Sorry, that I haven't stated the focus (I'm gonna edit the question) - it's more or less a question to gain a better understanding.