File + Database Transaction Security

Question

I have a MySQL table that basically serves as a file index. The primary key of each entry is also the name of a file in a directory on my website.

When a user wants to delete a file from the system, I want to ensure some transaction security, that is, if something goes wrong, as soon as you delete the file, the record will not be deleted, and if for some reason the database server does not fade the file deleted. Any event will be very unlikely, but if there is even the slightest probability of a problem, I want to prevent it.

Unfortunately, I do not know how to implement this. Should I work, which is less likely to fail, and just assume that it never will? Are there any known recommendations for this?

Oh, and here is the kicker - my web host only supports MyISAM tables, so there are no MySQL transactions for me.

In case that matters, I use PHP as a server-side scripting language.

Answer 1

Whether the file is "deleted" from the database via the UPDATE or DELETE rows, the problem is the same: database operations + files are not atomic. Neither UPDATE nor DELETE are safer than others, these are both transactions in the database, while the file operation is not.

, . "", . , , , "". "" , , .

.

:

File Exists -- DB Record exists -- Truth
    Yes             No             File does not exist
    Yes             Yes            File does exist
    No              Yes            File does exist, but its in error.
    No              No             File does not exist

, .

, , .

, . , , " ", 1. , A-OK

, , , . , . , . , - "", DB .

, " ", , , . , undefined, . , - .

DB .

, , - , , , -. , . ( ) , ..

Answer 2

, , , , , . , ( "" ) , " ".

, , , , .

. SQL, - , , , , :

CREATE VIEW MainTable(...) AS
    SELECT * FROM RenamedTable WHERE DeleteFlag = 'N';

, MySQL, . , , .

Answer 3

( "is_active" ) "" : 0 = , 1 = .

• , "" .
• , = 0.
• / Status = 1.