Re: MySQL dialect converter for PHP (or other language)

> If you don't know Oracle, why switch it? I think this will end badly..
>
> Jonathan

Why? Probably because he was asked to make his code run on Oracle so that Marketing can claim the software is "database agnostic".

They will find out the hard way that there is no such thing as "database agnostic" code. When I hear this claim, the first question I ask is about the differences in the locking mechanisms and how they reconcile those critical differences.

Since Oracle is using its undo mechanism to serve data to a reader as of the moment its SELECT started (thus, not blocking a reader when there is a writer) and MySQL or MS SQL Server simply block a reader while another session is updating, I wonder how those so-called "database agnostic" applications behave under serious workload of writers and readers... Maybe they allow dirty reads in MySQL? Maybe they rely on the fact that a reader will always be blocked if another session is updating the data concurrently? Who knows? Since dirty reads never happen with Oracle (by design) and since a SELECT is NEVER blocked by a concurrent UPDATE, there is no way to reconcile those different behaviors (if you know how to do it in an "agnostic" fashion, let me know).

My bet is that the numbers you read in the application will become either totally unpredictable -or- will depend on the database engine running underneath. So much for being "agnostic".

The code MUST be aware of how the database handles locks to handle transactions correctly. As Tom Kyte always says: If you don't pay attention to the locks when accessing your data, you will corrupt it, it is GUARANTEED.

Melfar, I think you need to bring an Oracle professional on your team to lead the conversion to Oracle and the testing (by designing key scenarios to test concurrency) or you will get "strange" results when you deploy your code on Oracle in a real production environment (i.e. will lots of concurrent writers and readers). Received on Fri Feb 09 2007 - 18:05:26 CST