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.

asked 13 Oct '11, 12:08

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 14 Oct '11, 05:00

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

(02 Nov '11, 07:05) Volker Barth

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.

(02 Nov '11, 12:16) AlexeyK77
Replies hidden

...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).

(02 Nov '11, 12:42) Volker Barth
Comment Text Removed

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.

(02 Nov '11, 17:15) AlexeyK77

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:)

(02 Nov '11, 17:56) Volker Barth

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

permanent link

answered 14 Oct '11, 03:09

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

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

(14 Oct '11, 03:17) Volker Barth
Replies hidden

So performance is the key of your question.

(14 Oct '11, 04:50) Martin

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.

(14 Oct '11, 04:56) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×26
×23
×17
×6

question asked: 13 Oct '11, 12:08

question was seen: 5,529 times

last updated: 02 Nov '11, 17:56