Re: Delete query returns true value but still delete doesn't occur

On 28.03.2017 15:59, kushal bhattacharya wrote:
> I am running delete query on some table as
> delete from msg_table where msg_id=? and (state=? and sock_fd=?)
> the return values gives true

This is nonsense. Executing a query does not give "true" or "false". It may result in an error or it may be successful. And in the second case you will get the "number of affected rows" that will tell you, how many rows have been really deleted. This number might be zero.

> most of the time the query works and deletes the entry.
> But in some rare occurences,my return value is true but still the row is
> not deleted.

Nonsense again. If the query deleted no rows, then because there were no matching rows at the time that the query was run. One main aspect of databases is, that they are typically accessed concurrently by multiple users or sometimes even multiple threads inside the same application. In that context some queries will be run "at the same time" [1] and then the results can be unexpected to the inexperienced user (that means you).

If your delete didn't delete a certain row, but your following select shows one, it would just mean that it was inserted between those two statements by a third party. If the application uses transactions, then it might even been inserted before the delete, but committed only afterwards. The delete did not "see" the uncommitted row. This is called "transaction isolation" and I suggest you google the term.

[1] actually the database will *not* run all queries at the same time, this happens only for non-locking reads. Writes and locking reads sometimes have be serialized in a certain order, based on transaction constraints. Received on Wed Mar 29 2017 - 10:21:24 CEST