|
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. |
|
What about the Lock Table Statement? You have control independent of the current isolation level. 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
|
If there are any more insights, dear experts, I'd really like to hear them:)
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.
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).
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.
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:)