Adding a transaction to MySQL is equal to MaxSQL.

At the open source conference sponsored by OReilly in last August, MySQL developer MontyWidenius announced a new project called MaxSQL, a popular enhanced version of MySQL database. The most important thing is that MaxSQL integrates the latest BerkeleyDB Library from Sleepycat, because SyntaxHigh

At the open source conference sponsored by OReilly in last August, MySQL developer Monty Widenius announced a new project called MaxSQL, a popular enhanced version of MySQL database. The most important thing is that MaxSQL combines the latest Berkeley DB Library from Sleepycat Software, so the program supports transaction processing with another table type.

Currently, you cannot directly install the binary version of MaxSQL. you must compile it from the source code of MySQL 3.23 beta. However, when you see this article, you can download MaxSQL from the MySQL website. If you want to compile the latest version by yourself, please refer to the article "compile your own MaxSQL", where I described the compilation steps in detail.

What is a transaction?

Why is a new project name required for a transaction-safe table added? Maybe I should mention that the primary MySQL developer Monty Widenius has two children, one is My and the other is Max. The two are competing with each other and there is another rival PostgreSQL.

Regarding PostgreSQL, another article in this article "PostgreSQL adds a level for e-commerce" will mention that it is a competitor that is beneficial to us, because each project team is overcoming its product weaknesses. What MaxSQL does is to eliminate the gap between the two competitors. MySQL has fast read operations, so it is very popular with Web developers because the Web content is mainly read. However, on the other hand, because MySQL uses an ISAM table, you must lock the entire table during write operations, which slows down the update and insert operations, when the traffic is large, it will be a big problem. When the number of requests per second increases, write operations are queued on the server, and a timeout error occurs on the Web page.

After MaxSQL is used, you can still create a normal quick ISAM table, or select the new BDB type when a table needs to use the transaction security feature.

BDB tables are much faster in writing than ISAM. Because they do not use table-level locks. In addition, the processing of any transaction table is logged to prevent hardware failure. Logs allow the COMMIT/ROLLBACK operation. For example, if you place an order in an online store, the order will record a row in the "order" table. The system will remove the corresponding inventory goods quantity from a row in the "inventory" table. If you use the MySQL ISAM table, a CGI program needs to perform the following six steps:

LOCK inventory table
LOCK order table
UPDATE inventory table
UPDATE order table
UNLOCK inventory table
UNLOCK order table

If someone locks any table, this CGI program will have to wait. After both of them are locked, CGI can update them and release the lock. If step 3 fails (for example, server downtime), the order table will not be updated and your inventory has been reduced.

After using a MaxSQL BDB transaction security table, you only need to perform the following four steps:

BEGIN
UPDATE inventory table
UPDATE order table
COMMIT

You don't have to wait to release the lock. all four steps are one transaction. As long as you read the BEGIN statement, MySQL reads the command into the buffer until you see the COMMIT command. Therefore, all operations occur at the same time. Even if an unexpected operation occurs (the disk is full or the power is down), the database will not be destroyed. In a non-transaction security system, if the third step fails, the database will be inconsistent. in the BDB table, if the operation on the order table fails, MySQL will restore the operation on the database and table, so there will be no inconsistency.

Many websites use earlier versions of MySQL to implement table locks, but after using MaxSQL, it will be easy and fast.

What new weapons are there?

In addition to converting a BDB table into a MaxSQL table, there are also some important changes. One of the secret weapons is database replication. you can use a server as the master server and configure any number of slave servers. in this way, updates to the master server will also be copied to the slave server. To use such a system, change your CGI script to let them know the existence of MySQL slave server. This script also switches to the slave server when the master server cannot be connected.

Note that such a system is effective for databases with few write operations, because replication is not synchronous. If someone starts a transaction on the master server and the server crashes before the transaction ends, the master server does not have time to copy updates to the slave server. In this way, this system is not suitable for e-commerce, and CGI scripts always connect to the master server first, so it is not a load balancing system.

Another major improvement is the ISAM table format. The default table type is MyISAM. This type of table can process a maximum of 2 GB for each table and can be used across platforms. You can copy a MyISAM file from Linux to Solaris without any conversion.

Conclusion:

MaxSQL can be used as an excellent alternative to expensive business systems. Many systems do not need to handle a lot of transactions, but require Oracle installation. The transaction security table provided by MaxSQL makes it easier to update the programming of the database.