Thread: atomicity

Is it true that the current version of PostgreSQL does always respect
atomicity of transactions and MySQL does not?

Hugo Coolens <hugo.coolens@skynet.be> writes:

> Is it true that the current version of PostgreSQL does always respect
> atomicity of transactions and MySQL does not?

The former is true; the latter I don't know about (MySQL has recently
added "transactions" but I don't know any details about how they
actually work).

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

> Is it true that the current version of PostgreSQL does always respect
> atomicity of transactions and MySQL does not?

MySQL only supports transactions on INNODB tables. Therefore if your
transactions includes other tables, we doubt atomicity works. Note this might
change with MySQL 4.1.

PostgreSQL offers the ability to build "intelligent" database with
server-side programming (PLperl, PLpgSQL, ..), triggers, rules, views,
operators... It is a real development tool.

In a complex environment, PostgreSQL can be optimized at ***software level***
(i.e. the way you build the database and program is server-side). MySQL can
only be optimized at ***OS level*** or ***Harware level***.

OS and Hardware level optimization can speed up an application by a factor of
2. Software optimization has no limit other than your imagination.

On the converse, MySQL is just an "dumb" database only running simple SQL
queries. Furthermore, it has very poor ODBC support and will only work in an
Apache environment.

If you are new to PostgreSQL, maybe the best thing to do is play with it
using pgAdmin2 GUI, which can be found at http://pgadmin.postgresql.org.

Best regards,
/Jean-Michel POURE

Good Day All,

On Wed, 2002-02-27 at 01:28, Jean-Michel POURE wrote:
> > Is it true that the current version of PostgreSQL does always respect
> > atomicity of transactions and MySQL does not?

  The latest version of MySQL provides the option of supporting
  ACID-compliant transactions and row-level locking

> MySQL only supports transactions on INNODB tables. Therefore if your
> transactions includes other tables, we doubt atomicity works. Note this might
> change with MySQL 4.1.

  MySQL supports transactions with InnoDB tables and DBD tables. Users
  are provided with the choice of two different transactional table
  types and two non-transactional table types - one file-based (MyISAM),
  one memory-based (HEAP). The MySQL manual details this information.

  Users can choose the table type that best suits their needs.

> PostgreSQL offers the ability to build "intelligent" database with
> server-side programming (PLperl, PLpgSQL, ..), triggers, rules, views,
> operators... It is a real development tool.

  I agree that PostgreSQL is a good development tool that has features
  that MySQL does not yet possess (or that are more convenient to use in
  PostgreSQL than MySQL).

  I also believe that other databases, including MySQL are 'real'
  development tools.

> In a complex environment, PostgreSQL can be optimized at ***software level***
> (i.e. the way you build the database and program is server-side). MySQL can
> only be optimized at ***OS level*** or ***Harware level***.
>
> OS and Hardware level optimization can speed up an application by a factor of
> 2. Software optimization has no limit other than your imagination.

  This is an incorrect characterization of MySQL. Users have great
  control over the behavior of queries and the behavior of databases.
  These include options like delayed inserts, memory-stored indexes,
  packed indexes for faster index reading, hash-based tables stored in
  memory, read-efficient read-only databases, database replication, User
  Defined Functions, etc.

> On the converse, MySQL is just an "dumb" database only running simple SQL

  MySQL has a large, well-tested feature set that includes fewer
  traditional RDBMS features that PostgreSQL. PostgreSQL and MySQL come
  from very different traditions. PostgreSQL is the heir to quite
  phenonmenal amounts of academic research work that focused on
  developing a complete set of RDBMS features. MySQL evolved from a set
  of working development tools - it was developed with attention to
  speed, stability and performance at the cost of certain features.

  As PostgreSQL works to become a more practical development tool that
  requires less maintenance and is more robust, MySQL works towards
  having a larger set of features that are as robust as the current set.

> queries. Furthermore, it has very poor ODBC support and will only work in an

  To be specific, MySQL supports ODBC levels 0-3.51 - which is more than
  PostgreSQL supported last time that I checked.

> Apache environment.

  This is incorrect. In addition to the source, we distribute binaries
  for the following platforms:

  - Linux
  - Windows 95/98/NT/2000/XP
  - Solaris
  - FreeBSD
  - MacOS X
  - HP-UX
  - AIX
  - SGI Irix
  - Dec OSF

> If you are new to PostgreSQL, maybe the best thing to do is play with it
> using pgAdmin2 GUI, which can be found at http://pgadmin.postgresql.org.
  I would strongly consider reading Bruce Momjian's excellent book
  (See http://www.ca.postgresql.org/docs/awbook.html)


--
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Zak Greant <zak@mysql.com>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB,  Advocate
/_/  /_/\_, /___/\___\_\___/   Calgary, Canada
       <___/   www.mysql.com   403.244.7213

Le Mercredi 27 Février 2002 10:40, Zak Greant a écrit :
> Good Day All,
> Blablabla

Dear Zak,

MySQL is only good at one thing : marketing and propaganda.

Question : is your last mail a template? Was your previous mail generated
automatically? I already received dozens of such emails when trying to
unregister MySQL lists.

This does not work any more with me, sorry...

/JMP

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> The latest version of MySQL provides the option of supporting
> ACID-compliant transactions and row-level locking

Just to clarify, by "latest version", we are talking about the
MySQL 4.0 alpha release, correct?

Thanks,
Greg Sabino Mullane  greg@turnstep.com
PGP Key: 0x14964AC8 200202270853

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8fOs1vJuQZxSWSsgRAumQAKCjyT+bvWbcJFiwZRCaon+T4INPZgCfbmIh
dqvKEdKcKfSdaubeLq3gkQk=
=PQxP
-----END PGP SIGNATURE-----

Hello Zak,

ZG>   The latest version of MySQL provides the option of supporting
ZG>   ACID-compliant transactions and row-level locking

ZG>   MySQL supports transactions with InnoDB tables and DBD tables. Users
ZG>   are provided with the choice of two different transactional table
ZG>   types and two non-transactional table types - one file-based (MyISAM),
ZG>   one memory-based (HEAP). The MySQL manual details this information.

MySQL's ridiculous way of handling transactions prompted us to
look at postgres, infact we incurred some amount of data loss as well.
MySQL was used for financials, and I was in real soup when I lost
the data.

As a matter of fact, MySQL didn't even recommend the use of
transactions in some of the documentation and if I am not wrong they
don't guarantee for the usage of Berkeley DB (from sleepycat) for
production environment. InnoDB is no big deal it is much
slower.


ZG>   Users can choose the table type that best suits their needs.

Is this a plus point to use MySQL, i find it intriguing

ZG>   I also believe that other databases, including MySQL are 'real'
ZG>   development tools.

I still believe MySQL is best for websites and not for time-critical
financial applications. MySQL community support, i should say is
amazing.

ZG>   MySQL has a large, well-tested feature set that includes fewer
ZG>   traditional RDBMS features that PostgreSQL. PostgreSQL and MySQL come
ZG>   from very different traditions. PostgreSQL is the heir to quite
ZG>   phenonmenal amounts of academic research work that focused on
ZG>   developing a complete set of RDBMS features. MySQL evolved from a set
ZG>   of working development tools - it was developed with attention to
ZG>   speed, stability and performance at the cost of certain features.

Atleast you acknowledge the fact that postgreSQL has evolved through
much more mature process than MySQL.

--
Best regards,
 Gurudutt                            mailto:guru@indvalley.com

Life is not fair - get used to it.
Bill Gates

>   I am taking this off-list -- no point in wasting everyone's time with
>   this. I don't mind that you got in the parting shot in a public
>   forum.:)

Do not hesitate to post this answer to a public forum.

>   In the future, I would recommend that you provide hard facts to back
>   up your opinions - if you do not, sensible people will discount your
>   opinion as being uninformed.

Is MySQL able to run complex queries with 4-level nested LEFT and RIGHT
JOINS, perform complex actions using a database SERVER-SIDE language (Perl,
PLsql or whatever), run TRIGGERS to perform actions, REWRITE complex queries
using RULES, display VIEWS, altogether, come back to me and we will have a
beer.

Also, please note that your level 3 odbc driver does not work well. Do you
provide an enhanced GUI like pgAdmin2? This is not a question of technical
facts, PostgreSQL wins. Not a single serious programmer would trust MySQL for
all databases of a large company (I mean "all").

The problem is that you, at MySQL.com, are feeding the database user
community with:
- pre-written emails,
- pre-compiled marketing information (INNODB, "Transactions"),
- pre-written benchmarks sent to journalists (I noticed these were always the
same benchmarks...).

The main difference between "you" and "us" is that MySQL is a company and we
are a community. We don't whaste time sending marketing information...

Have a look at your homepage. This is not serious :

+ MySQL a winner in server database clash - 27 Feb 2002
In a recent database benchmark test performed by Ziff Davis Media Inc., the
company behind PC Magazine, eWeek and other well-known publications, MySQL
stands out as a winner.
= You sent them a benchmark and they published it. Tell me, how many people
are in charge of Journalists at MySQL (specialists, developpers, students,
how many?). At least the team who wrote this homepage. So, how many?

+ Letter from the CEO - 08 Feb 2002
= HaHaHa

+ Enhanced MySQL ODBC driver released - 31 Jan 2002
= Do you mean it never worked until 31 Jan 2002. I lost so much time with
your odbc crap.

+ U.S. Census Bureau Reaps Awards from MySQL-based Web Sites 14 Jan 2002
= Propaganda. Again, who wrote this article? Is it what you call an article?
Do you send pre-written articles to journalists?

+ Name the Dolphin has Ended - 10 Jan 2002
= Propaganda

+MySQL pushes Yahoo! into InfoWorld 100 list - 30 Nov 2001
= HaHaHa

+Leading European VCs Invest in MySQL AB - 12 Nov 2001
= And you really believe you can survive the PostgreSQL wave?

This is brain-wash my friend. So please brain-wash people on MySQL list, not
here, please.

Regards,
Jean-Michel POURE

Le Mercredi 27 Février 2002 17:07, Jean-Michel POURE a écrit :
> Is MySQL able to run complex queries with 4-level nested LEFT and RIGHT
> JOINS, perform complex actions using a database SERVER-SIDE language (Perl,
> PLsql or whatever), run TRIGGERS to perform actions, REWRITE complex
> queries using RULES, display VIEWS, altogether, come back to me and we will
> have a beer.

Sorry I forgot something: MySQL adds ridiculous transactional support.

> ZG>   MySQL has a large, well-tested feature set that includes fewer
> ZG>   traditional RDBMS features that PostgreSQL. PostgreSQL and MySQL come
> ZG>   from very different traditions. PostgreSQL is the heir to quite
> ZG>   phenonmenal amounts of academic research work that focused on
> ZG>   developing a complete set of RDBMS features. MySQL evolved from a set
> ZG>   of working development tools - it was developed with attention to
> ZG>   speed, stability and performance at the cost of certain features.

MySQL surely employs a team of communication specialists. This kind of
argument can only be invented by a non-technician, non-programmer, non-RDBMS,
"marketing" someone. All this reminds me of my MBA.

> MySQL AG employs a REAL team of FAKE communication specialists
> whose goal is to sell a FAKE product to a team of FAKE investors,
> who in turn will  resell this FAKE company to a team of REAL people
> who in turn will loose their REAL money. And cry...

/JMP