Does PHP's Oracle PDO hang on DELETE?

Question

This hangs in Php (5.2.6-Win32 + Oracle10g) is it a bug, or I'm doing something fundamentally wrong?

try {
    $conn = new PDO($DB,$USER,$PASSWORD);
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    //connected
    try {
     $conn->exec("DELETE FROM MY_TABLE");
     echo "done";
...

Note: I don't know if this is a bug or not, I'm guessing it is. However as I couldn't find a bug report for this after a quick search. I might be doing something wrong, so I figured I'd ask here first, and for posterity in case someone else has a similar issue.

Answer 1

Pretty unlikely that it's hanging since PDO seems to be in use quite a bit, though how much with Oracle, I don't know.

A delete from my_table may take some time depending on how many records there are. How long did you wait before deciding it was hung and how many records were in the table?

Answer 2

Are there other activities going on in the database at the same time? If yes, I'd suspect deadlocks. Some uncommitted DML transaction(s) might be locking some rows of the MY_TABLE.

If you don't need rollback capability, consider using TRUNCATE instead of DELETE.

Answer 3

A DELETE without a WHERE will (most likely) a full scan of the table. That means it reads every block under the 'high-water mark' - that is every block that has ever been used by the table. So if the table was, at one time, very big, then it can take a very long time to delete just a handful of records. [Because it doesn't 'know' there are only four records until it reads all that space.]

A truncate doesn't do this. It simply moves the high-water mark of the table so that any blocks that were in use quickly become unused.

Answer 4

If there is a transaction running on the same table, it might block your DELETE.