patternsqlMinor
Using MySQL triggers or transactions?
Viewed 0 times
transactionsmysqltriggersusing
Problem
I want to ask your opinion in using MySQL triggers or transactions in a website.
Actually I have a history
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
Here is the problem. How can I be sure that the total credit amount form the
I thought using 2 methods:
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#3397, answer score: 8
Revisions (0)
No revisions yet.