Using MySQL triggers or transactions?

Question

I want to ask your opinion in using MySQL triggers or transactions in a website.

Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So each payment operation is inserted in this table.

However, it will be time consuming calculating each time the total credit amount of the user every time he do action. So I thought maybe is a good idea to keep the total credit amount for each user in a user profile table.

Here is the problem. How can I be sure that the total credit amount form the profile table will stay synchronized with the operations from payment history table ?

I thought using 2 methods:

• MySQL triggers or
• transactions coded in the source code

Which is more reliable? What if I have large database (over 100.000 users) ?

Do you have any suggestions to do this?

The BD MySQL engine is InnoDB.

Answer 1

Without a doubt, I would rule out triggers and strictly stay with transactions.

Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.

Combine this with the fact that proper data validation protocols are not implemented in MySQL's Stored Procedure Language. Business Intelligence is OK to have contained in a database provided the Stored Procedure Language can handle a transactional environment. As a MySQL DBA, I have to honestly say that such is not the case with MySQL. Oracle (PL/SQL), PostgreSQL (PL/pgSQL), and SQL Server (T-SQL) have this edge over MySQL.

Concerning transactions, MySQL has InnoDB as its main ACID-compliant storage engine (Deafult storage engine in MySQL 5.5). It has excellent crash recovery and obeys the ACID compliance protocols.

I would choose transacitons over triggers every single time.

Answer 2

I agree with Rolando's assessment. Your business logic should reside in your application and should make changes to the database transactionally.

Scaling to 100,000 users, of course, depends on your application and the database traffic it generates. With MySQL under a heavy transactional write load, you might be soon faced with the burdens of sharding and/or replicating your dataset in order to maintain acceptable application response.

However, there are alternatives to sharding MySQL. One of them is Clustrix (my employer) which is a parallel-scalable high-performance single-instance SQL database system. It's a clustered database system that presents itself as a single MySQL server. Scaling the database described in this thread seamlessly to 100,000 users on a single instance of Clustrix would require no sharding and no additional application logic.