Problem executing delete statement

[usamaalam]

Hello everybody,

When I run the following statement, it returns 3660 rows.

select * from symbols where code = 'AAHSY00';

But when I run the following statement, it doesn't do anything and goes in a never ending loop. delete from symbols where code = 'AAHSY00';

Any idea on how to fix this?

Thanks.

[nav33n]

Nothing wrong with the query and it shouldn't go in an infinite loop. Can you post your script ?

[usamaalam]

There is no script and I was running the queries on command line. I found that select queries were running but insert and delete queries just stucked for hours. I stopped and started mysql service and it is good now.

Thanks.

[verruckt24]

If your select queries work but deletes and inserts take time it should have been a problem of locking. Basically it could have been a case where one of the previous operations put a lock on the table which wasn't released, hence the delete query could never obtain the exclusive lock it needed and hence kept waiting. Yet in this situation you should have got a "Lock wait timeout exceeded" error. Your mention that it was sorted on a restart of MySQL server makes me believe more about this being related to locking.

It would be good if you could take a look at the Table Type/ Storage Engine for your table and check what type of locking scheme it supports, it would certainly help understand and hence sort out future such incidents.

[vankysrox]

i have a problem in deleting query command, actually i am using grid view in which i m using edit field and in edit button i write update command and delete command (actually wht i m doing is during updation if the value entered in textbox is less than the existing values it delete all the records above that value) both query works correctly but when it come s to delete.executenonquery() cursur stuck here for 5-6minutes or more time
how can i overcome from this problem
pplz help soon

[vankysrox]

SqlCommand cmd1 = new SqlCommand("update product set prod_name='" + tb1 + "',plan_no='" + tb2 + "',terms='" + tb3 + "',sum_assured='" + tb4 + "',min_age='" + tb5 + "',max_age='" + tb6 + "'where prod_id='" + tb7 + "' ", con);
        con.Open();
        int x = cmd1.ExecuteNonQuery();

        for (int i = no1-1; i < no1; --i)
        {
            if (i < no1)
            {
                SqlCommand del_rec = new SqlCommand("delete from prod_premium where prod_id='" + tb7.ToString() + "' and age='" + i.ToString() + "'", con);
                del_rec.ExecuteNonQuery();
                if (i ==0)
                {
                    break;
                }
            }
         }
// this is the source code

[nav33n]

I am not sure about the logic you are using. Check your loops to see if its not looping unnecessarily. Print your query and see if its the query that is failing. :)

[vankysrox]

i m quite new in using CURSOR in sql server 2005. i want to select left_child and right_child according to parent(mem_id) and then do the same with its left_child and right_child to make a complete tree till the end. one child is also accepted . plz help soon
thx in advance