the commercial database market is dominated by three players:
ibm db2
oracle 9i, 8i, etc.
microsoft sql server

in the open source world the major players are mysql and postgres.
the pros/cons of mysql:

previous versions of mysql did not support transactions, and only supported table level locking (didn't support row level locking). however that is no longer true if you use the correct type of table.
http://www.mysql.com/doc/en/Locking_methods.html
http://www.mysql.com/doc/en/Table_types.html
mysql doesn't support subqueries

mysql doesn't have a procedural language or support triggers.