Weird Query Hangs

[niravpatel]

I am running Coldfusion 6, and I am trying to do debug an action page for a
form. Basically, I isolated the problem to one query, and then I simplified the
query to just do this: "UPDATE table SET PK=1 WHERE REC_NUM=6" Even this
simple update query does not work. I run the query and the server grinds to a
halt, it takes for ever and never does anything. The error logs dont really
help. I decided to change the query to an insert on the same table, that query
works without any problems. I also put in the query into another DB app (Toad),
and the query works fine, and I doubt my sql is off on those three lines. I
have no idea what is going on. Anybody have any idea why such a simple query
would fail, but an insert query would work?

[jorgepino]

can you post some code and the enviroment you are using

[jorgepino]

REC_NUM=6 is that the record ID?
does the rec_num 6 exist?

[CF_Oracle]

It could be problem if you are trying to update primary key or column having
UNIQUE KEY constraint and value 1 allready exist.
Another reason - child record(s) exist in one or more tables and it's
checking for it and prohibits to do but usually error message indicates that.
Also check with DBA user permissions for this table. Maybe you have INSERT
but not UPDATE rights (strange b ut possible).

[niravpatel]

Sorry, I tried to change the titles of the cols up, the "PK" field is not a
Primary Key, the primary Key is REC_NUM, and there are no unique constraints on
the PK column. Also, All permissions are set for Select, Insert, Delete,
Update. There are no child record constraints either. Also, REC_NUM 6 does
exist, the SQL i posted is fine and works when I directly connect to the DB
Server (using direct SQL).

Also, I found that when i try to run the SQL as a script through TOAD it
hangs, and if i wait for 15 minute on CF server, I get a Execution timeout
expired Error for the query, with SQL STATE HYT00

[JMGibson3]

Get your DBA involved. Since it also fails in TOAD, it must be something wrong
with the database itself, perhaps a Trigger or Function that has a loop, or
maybe a Cascade Integrity rule that recursively spins out. At least you now to
look for UPDATE rather that INSERT events.

[niravpatel]

I have talked with the DBA, and there are no constraints being used right now on the table (we got rid of all of them to debug). Any other possible culprits?

[jorgepino]

can you post ther error you are getting?

[JMGibson3]

You mentioned constraints explicitly in your discussions with the DBA. Did you
explicitly chase down Triggers and Functions they may in turn call?
For example ( I'm assume it's Oracle due to Toad reference, and also my Oracle
is rusty and no examples available):

If your table has an OnBeforeUpdate(trigWhatever) Trigger defined (or
OnAfterUpdate) then that Trigger WILL be fired.
If that Trigger has an inadvertant loop the Update query would never return.
Triggers can get as horribly complicated as anyone could wish (i've had to mess
with a few bad ones). Perhaps the trigger has an outdated reference to a no
longer existing Oracle instance or a table with 10 million rows or calls a
function to Update something else which also has a trigger which recurses or
deadlocks or .......................