I have a function that deletes all the values in a table and then
inserts the data again. If this function is run in 2 sessions at the
same time then it populates it twice, giving me a unique value error,
because one of the fields is supposed to be unique.
I understand this because the second time it is run, the function does
not see the delete as the first transaction is still running, so it
deletes the same data the first one deleted and does not know that the
new data exists when it starts to delete. I need a table level lock to
prevent this from happening.
I have a second function that takes 3 time as long to run and also
includes a delete and insert statement as well as a lot of other stuff.
The weird thing is if this function is run in two separate sessions at
the same time the data is not duplicated.
There is no lock and I don't have a unique index on this table and when
the process is finished running there is the same number of records
whether I run it once or twice. This is how I would like it to run, but
I have to know why it works, otherwise I'll be worried that it will bite
me in a couple months because something surfaces that I didn't look at
before.
I have tested it in PGAdmin. The first staements of the function are
delete and insert. When I run just the delete and insert at the same
time, then it puts in double the records. When I run the function at the
same time then it only puts in the records once. Its as if the function
is waiting for the first function to completely finish before starting.
The function that duplicates (and causes the error) is just a straight
delete and insert. The one that doesn't includes a for loop on a select
with an update statement (on the same table it is selecting from) in the
loop AFTER the delete and insert statements.
Does this make any sense?
