Sql – DB down on executing delete query

cascadecascading-deletesdatabasesql

Is there a possibility that a simple delete query can bring down a DB?

We executed a delete query (single row deletion) and that query hung. When multiple people tried executing the same delete again, the Oracle DB is down.
Multiple tables reference this table and a cascade delete was not used. Should a cascade delete have been used?

What are the possible reasons for the DB to go down on executing this sql?

Best Answer

The delete stmts hung, the db reached max number of allowed processes (which were waiting); and the db stopped establishing new connections. We killed the hanging processes and the db is up and running now.

Related Solutions

Sql-server – Multiple Indexes vs Multi-Column Indexes

I agree with Cade Roux.

This article should get you on the right track:

One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column. Basically it helps your data insert at the end of the index and not cause lots of disk IO and Page splits.

Secondly, if you are creating other indexes on your data and they are constructed cleverly they will be reused.

e.g. imagine you search a table on three columns

state, county, zip.

you sometimes search by state only.
you sometimes search by state and county.
you frequently search by state, county, zip.

Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used (by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

By the way We can take advantage of the fact that with Multi-Column indexing the first index column is always usable for searching and when you search only by 'state' it is efficient but yet not as efficient as Single-Column index on 'state'

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.

The article will help a lot. :-)

MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

Beginning with MySQL 8.0.19 you can use an alias for that row (see reference).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS new
ON DUPLICATE KEY UPDATE
    age = new.age
    ...

For earlier versions use the keyword VALUES (see reference, deprecated with MySQL 8.0.20).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...

Best Answer

Related Solutions

Sql-server – Multiple Indexes vs Multi-Column Indexes

MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

Related Topic