November 20, 2008 at 11:07 am
Christopher Stobbs (11/19/2008)
IS this working with the change to the insert statement yet?
Hello again,
No is not, unfortunalty...
November 20, 2008 at 11:42 am
Can you post the code again with the things I recommended?
TIA.
November 21, 2008 at 11:13 am
Ninja's_RGR'us (11/19/2008)
Even with a transaction if you run your code like this :
begin tran
if not exists (Check if exists)
begin
insert into...
end
commit tran
There's a tiny delay, just long enough where a second call to the sp could be started and run right in between the moment where the if is evaluated and the insert into is executed.
The solution to this problem was quite simple, change the statement to this :
begin tran
insert into table (columns) Select columns, parameters where not exists (check exists on base table here)
commit tran
Wut ?
In the first transaction, should not all tables involved be locked until the trsnsaction is commited ? Thus blocking concurrent access ?
And what need is there to place the modified statement in a transaction ? After all, it should be ATOMIC, i.e. it must proceed as a complete statement or fail
November 21, 2008 at 11:19 am
No, the server will only lock the row while the write is in progress... it will only lock the table if a massive amount of rows need to be written to. You can do that manually but that would cause more problems than anything else.
That's why that little trick I gave is so usefull, it's a super quick transaction and it insures uniquenness.
Did you test to see what happens if you click twice on the button that saves the data in a very quick fashion?
Maybe 2 queries are sent at the same time, with just enough difference that the server sets 2 dates (with 3 ms difference)?
November 21, 2008 at 11:31 am
No,
I did not try what you suggested.
What I did just try was to begin a transaction in a query analyzer window then do a select on a table.
I then started a second query analyzer window and tried a select on the same table.
No waiting, the seoncd QA window listed what the select statement ordered.
So you are right.
So much for my illusion that a transaction should prevent something like that...
As for my second question, I still do not know why the statement has to go in a transaction.
November 21, 2008 at 4:20 pm
The transaction blocks a second person from doing the same insert at the same time (that's why the insert and the exists are done in the same DML statement... the row will be locked during the write process, hence blocking anybody wanting to insert the same data at the same time)... this is only true if you have some sort of constraint on the table that would error out if that ever happened.
This is usefull to show user friendly error message and handle things in a nice way. That'S the real
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply