I just had a coworker call me over to her desk and show me an insert script she
had tried to run (10000 lines). When ran it in Toad 9.7.2.5, the insert script
modified itself and added the following code between each insert statement.
BEGIN DBMS_LOCK.SLEEP(1); END;
Has anyone else seen this or possibly know why this happened? I have never seen
this before.
Thanks,
Lynn Olpin
E-Mail: Lynn.Olpin@hill.af.mil
We don’t have that in our code anywhere. You don’t have that in a
code template or something like that do you?
No. That what is so weird.
I don’t think that Toad did it, something else must have happened.
Thanks,
Lynn
Maybe someone added that line to the login.sql or glogin.sql and that’s
how it’s getting added (see toad options)
waiting (a minimum of) 1 second for a DML lock is’nt really all that unusual
(unless of course you’re bulk-inserting which apparently is not the case here)
Norm/Roger or anyone know why this would be the case?
Martin
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l’information seulement et n’aura pas n’importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
Morning all,
waiting (a minimum of) 1 second for a DML lock isn't really
all that unusual (unless of course you're bulk-inserting which
apparently is
not the case here)Norm/Roger or anyone know why this would be the case?
Calling DBMS_LOCK.SLEEP(1) is not waiting one second for a lock, it is
inserting a pause of one second between INSERTs.
Strangely enough, I came across something like this very 'problem' just
last week. It made me so angry!
I had a release on a system which had (random) commits all the way
through it, or so it appeared. IN light of our SQL scripting standards,
I advised the people responsible and removed the commits. The script
failed with duplicate PK values (in a trigger), so I abandoned the
release, rolled back and advised the developers of the problem.
Here's the laugh:
Updating the table resulted in the firing of a trigger to create a
history record in another table. The PK on the history table is the
updated table's PK plus a DATE. (Who can see the problem yet?)
So, when we run on a s-l-o-w server all is well. When we get onto a fast
one, the resolution of the DATE column is too large and allows duplicate
values. The solution from the Duh-veloper(s) in this case was not "lets
investigate and fix it" but "lets bung in a few commits to slow it down"
I was told (dangerous!) to put the commit's back into the script exactly
as they were and run it again. I refused. 
I was told that the script was running too fast and they commits helped
slow it down so that there were no duplicate values. Put the commits
back in!
I refused and pointed them at DBMS_LOCK.SLEEP() and lo, we were all
happy! Well, I was happy-ish as I also told them about a TIMESTAMP data
type which has micro-second resolution and even running in a tight loop,
can't generate duplicate values.
Anyway, the use of DBMS_LOCK.SLEEP(1) could be because the rows are
being inserted into a table that has a DATE column as part of its PK (or
some unique constraint) and the sleep is to ensure that no two rows are
inserted with the same SYSDATE value.
Maybe?
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
The lunatics are running the asylum again.
Why wouldn’t they consult a DBA for help when these types of design
challenges come up?
Hi Jeff,
The lunatics are running the asylum again.
As ever!
Why wouldn't they consult a DBA for help when these types of
design challenges come up?
Consulting a DBA is a task that is only (ok, usually only) carried out
when all else fails.
We have "architects" who decide on how an application will be built etc
etc, and they "know it all" and don't need to talk to the people who
will have to clean up the mess when it hits the fan at go live time!
(Ask me how I know!)
The problem is that in testing the app, they never made frequent
(enough) changes to the same row to be able to cause the duplicates, but
on running a maintenance script in plain SQL, they did.
So, rather than think, the simply added commits. It does make me wonder
what other "goodies" are hidden away waiting to bite someone.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Norm!
I refused and pointed them at DBMS_LOCK.SLEEP() and lo, we were all
happy! Well, I was happy-ish as I also told them about a TIMESTAMP data
type which has micro-second resolution and even running in a tight loop,
can't generate duplicate values.
Then you need newer hardware! 
Rich -- [TeamT]
Disclaimer: [Carefully insert TeamUSA reference here]
Greetings Lynn;
BEGIN DBMS_LOCK.SLEEP(1); END;
Sorry this response is a bit late, I was on holidays all last week.
Question of curiosity: what do you mean by “the insert script modified
itself”?
Do you mean:
They’ve never heard of using a Sequence for that part of the history
key??? (Or use a Sequence as the entire PK of the history table, with a
non-unique index on other-key-plus-date.)
Nate Schroeder
IT Commercial Technical Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167
314-694-2592
Yes, the DBMS_LOCK.SLEEP(1) does work well. Our problem was that the
script did not have the “Sleep” function in it. My coworker loaded the
script (without the DBMS_LOCK.sleep(1)) function, ran it and the
DBMS_LOCK.sleep(1) appeared between each insert. Must be gremlins.
Thanks for the find and replace functionality. I just had her find the
string and replace it with nothing to put the script back.
My coworker is not here today so I have not been able to see if it has
happened again.
Lynn
Sorry for the confusion.
Here are the steps.
My coworker opened the script in the SQL Editor and verified the script (no
“BEGIN DBMS_LOCK.SLEEP(1); END;” statements in the script.)
She then ran the script. The “BEGIN DBMS_LOCK.SLEEP(1); END;”
appeared in the script between each insert statement.
She noticed the changed script and called me over. I have verified that the
“BEGIN DBMS_LOCK.SLEEP(1); END;” did not exist in the original
script.
Somehow, the script was modified while running.
In addition to what John noted earlier that DBMS_LOCK.SLEEP doesn’t occur anywhere in the source, the Editor will prompt when a file has been modified outside of Toad. So, if the script were loaded from a shared resource and if it were edited by someone else the Editor will prompt to reload the modified file. It would be very easy to click yes or hit return and get whatever modifications were made elsewhere.
Michael
Did you check the login.sql and glogin.sql files pointed to by toad options and
those files contents???
I asked earlier and no reply….
If the text is appearing in the editor then login.sql and glogin.sql are not the
cause.
Michael
To me it sounds like it may be a macro. Does she have an Editor macro set to go
to the end of a line, enter a newline, and insert that text?
The only causes that I can think of are a macro being run, an external source
updated the file and the editor loaded it, or the user had that on the clipboard
and accidentally selected all text and pasted.
Michael
Bert,
I saw your e-mail, but I did not see your response as a question. It sounded
more like a statement to me (no question mark and the wording made it seem that
way to me). I will check her Toad tomorrow when she is here and let you know.
Lynn
Michael says it cannot happen this way – but I seem to recollect that when
run as a script we execute the contents of those commands – I just did not
think they showed up in the editor – but they would show up with set echo
on in the script output tab – that’s why I asked ……
Michael says it cannot happen this way – but I seem to recollect that when
run as a script we execute the contents of those commands – I just did not
think they showed up in the editor – but they would show up with set echo
on in the script output tab – that’s why I asked ……