There are four main open-source relational database management systems of note that might be appropriate to this sort of application: Postgresql, MySQL, Firebird and Ingres. There are other systems such as SQLite, but they do not have this type of architecture and are not really designed for this type of workload. Some other open-source database management systems of this type do exist, but do not appear to be strongly viable for some reason, such as a lack of apparent vendor commitment. An example of a system that has this type of issue is SAP-DB.
Postgresql has the best feature set of any of the open-source databases, and support for XA transactions, which you will probably want if your application is a three-tier system and supports transactions of non-trivial complexity. In particular you will want this if you want to do transactions spanning more than one call to the database.
Several commercial variants of PostgreSQL have been built over the years, such as Illustra, Greenplum and EnterpriseDB. Illustra was a commercial release of PostgreSQL which was subsequently bought by Informix. Greenplum is a mofified version designed for data warehousing applications. EnterpriseDB is a company that provides supported commercial versions of PostgreSQL with some value-added software.
MySQL 5.x has a feature set that supports a reasonable cross section of capabilities, but it is not as feature-rich as PostgreSQL. It has more widespread mainstream acceptance and would be the easiest of the open-source database management systems to recruit skilled developers for. Although older versions did not have robust transaction support, transactional storage engines such as InnoDB have been available for some time. The current politics surrounding the acquisition by Sun have generated code forks and the MySQL landscape is somewhat messy, with controversy about quality issues in the 5.1 release. However, MySQL is by far the most popular and best known of the open-source database management systems and is the only one with significant brand recognition outside of open-source circles.
Firebird is an open-source version of Interbase. Last I looked, it did not have XA support but would be fine if your application was set up as a two-tier client-server system. Update: I can't find a definitive specification on this, but the documentation does indicate that it has support for two-phase commit, but what I could find was not specific on whether it supported the XA protocol. The documentation implies that the JDBC driver does have support for two-phase commits.
An interesting variant on this system is Fyracle, which is designed to offer a degree of compatibility with Oracle. This was originally developed for use as a back-end to Compiere, which was built against Oracle and quite tightly coupled to it.
Ingres is now available with an open-source license, but has been greeted with a bit of a collective yawn by the open-source community. However It is quite feature-rich and very mature - I know people who were doing INGRES apps in 1990, and it dates back to the 1980s.