Archive

Posts Tagged ‘MySQL Primer’

Transactions & MySQL

September 29, 2009 Leave a comment

 

 

MySQL supports small, embedded kiosk-style applications as well as the occasional five billion-record data warehouse. This versatility is possible in part because of the MySQL engine, which has been designed for maximum scalability, maximum resource efficiency, and easy portability to various platforms and architectures. This section will discuss the important characteristics of this engine in detail.

 

Connectivity:

MySQL is designed on the assumption that the vast majority of its applications will be running on a TCP/IP (Transmission Control Protocol/Internet Protocol) network. This is a fairly good assumption, given that TCP/IP is not only highly robust and secure, but is also common to UNIX, Windows, OS/2, and almost any other serious operating system you’ll likely encounter.

MySQL does allow named-pipe connections, which were designed mainly to support network connections in earlier non-TCP/IP networks, such as LAN Manager and Windows NETBEUI. (NETBEUI uses an addressing scheme based on the NETBIOS machine name rather than a routable IP address.)

 

SQL:

The Structured Query Language (SQL) is an open standard that has been maintained by the American National Standards Institute (ANSI) since 1986. Although it’s true that the implementation of this standard does differ in varying degrees from vendor to vendor, it’s fair to say that SQL is today one of the most widely used cross-vendor languages. As with other implementations, such as SQL Server’s T-SQL (Transact-SQL) and Oracle’s SQL, MySQL has its own variations of the SQL standard that add power beyond what is available within the standard.

 

Data Integrity:

MySQL supports engine-level data integrity through the use of primary key and foreign key constraints. Columns can be defined so that explicit NULL values cannot be entered into them. To prevent empty columns, MySQL supports the use of default values, which, when combined with NOT NULL properties, ensure that valid data is entered into a column that would otherwise be left blank.

 

Transactions:

Until recently, MySQL was not known for its transaction-handling capabilities; however, since version 3.23, MySQL has been providing table handlers, such as InnoDB and BDB, that manage transactions in much the same manner as other commercial RDBMS products. A transaction-safe database system must pass what is known as the ACID test to qualify for compliance. An ACID-compliant database must support the following characteristics:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

 

Atomicity:

A transaction is defined as an action, or a series of actions, that can access or change the contents of a database. In SQL terminology, a transaction occurs when one or more SQL statements operate as one unit. Each SQL statement in such a unit is dependent on the others; in other words, if one statement does not complete, the entire unit will be rolled back, and all the affected data will be returned to the state it was in before the transaction was started. Grouping the SQL statements as part of a single unit (or transaction) tells MySQL that the entire unit should be executed atomically.

Atomic execution is an all-or-nothing proposition. All of the SQL statements must be completed for the database to maintain a state of data integrity; otherwise, none of the statements will be finalized and committed to disk. In MySQL, the beginning of a transaction is marked with a BEGIN statement. The transaction (or unit of work) will not be considered complete until a COMMIT command is issued to tell MySQL to complete the action. When necessary, the ROLLBACK command will initiate a rolling back of all changes to the state before the BEGIN statement.

An everyday real-world example of this can be found in the banking business. By debiting and crediting your bank account, your bank adds and subtracts money from the account within one transaction. These updates usually involve multiple tables. The bank would not be able to maintain data integrity without guaranteeing that the entire transaction will take place, not just part of it.

Transaction management is particularly important to client-server systems that perform data entry or to any application that must be able to count on a high degree of safety from undetected data loss, such as the banking example described here.

 

Consistency:

Consistency exists when every transaction leaves the system in a consistent state, regardless of whether the transaction completes successfully or fails midway.

For example, imagine that your bank uses a transaction that is supposed to transfer money from one bank account to another. If the transaction debits one bank account for the requisite amount but fails to credit the other account with a corresponding amount, the system would no longer be in a consistent state. In this case, the transaction would violate the consistency constraint, and the system would no longer be ACID-compliant.

In MySQL, consistency is primarily handled by MySQL’s logging mechanisms, which record all changes to the database and provide an audit trail for transaction recovery. If the system goes down in the middle of a transaction, the MySQL recovery process will use these logs to discover whether or not the transaction was successfully completed and roll it back if required.

In addition to the logging process, MySQL also provides locking mechanisms that ensure that all of the tables, rows, and indexes that make up the transaction are locked by the initiating process long enough to either commit the transaction or roll it back.

 

Isolation:

Isolation implies that every transaction occurs in its own space, isolated from other transactions that may be occurring in the system, and that the results of a transaction are visible only once the entire sequence of events making up the transaction has been fully executed. Even though multiple transactions may be occurring simultaneously in such a system, the isolation principle ensures that the effects of a particular transaction are not visible until the transaction is fully complete.

This is particularly important when the system supports multiple simultaneous users and connections (as MySQL does); systems that do not conform to this fundamental principle can cause massive data corruption, as the integrity of each transaction’s individual space will be quickly violated by other competing, often conflicting, transactions.

Interestingly, MySQL offers the use of server-side semaphore variables that act as traffic managers to help programs manage their own isolation mechanisms. These variables are useful in cases for which you prefer not to incur the overhead of the Transaction Managers, or when a recovery plan is possible outside of the confines of log recovery. MySQL InnoDB tables offer isolation in transactions involving multiple queries, while MyISAM tables allow you to simulate isolation via the LOCK TABLES command.

 

Durability:

Durability, which means that changes from a committed transaction persist even if the system crashes, comes into play when a transaction has completed and the logs have been updated in the database. Most RDBMS products ensure data consistency by keeping a log of all activity that alters data in the database in any way. This database log keeps track of any and all updates made to tables, queries, reports, and so on. If you have turned on the database log , you already know that using it will slow down the performance of your database when it comes to writing data. (It will not, however, affect the speed of your queries.)

In MySQL, you can specify whether or not you wish to use transactions by choosing the appropriate table handlers, depending on your application. The InnoDB table handler performs logging a bit differently than BDB does, while MyISAM does not support the type of logs that would permit you to be assured of a durable database. By default, InnoDB tables are 100% durable to the last second prior to a crash. MyISAM tables offer partial durability—all changes committed to the system prior to the last FLUSH TABLES command are guaranteed to be saved to disk.

SQL Server and Oracle, for instance, are able to restore a database to a previous state by restoring a previously backed-up database and, in essence, “replaying” all subsequent transactions up until the point of failure. These database products do not encourage the direct use of—nor do they expose the inner data structures of—the log files, because those files form part of the database engine’s recovery mechanism.

MySQL also keeps a binary log of all data manipulation activity in sequential order. However, unlike the logs used in other databases, this log is easy to read, which means that it’s a relatively straightforward task to recover lost data by using the last backup in combination with the log.

 

Links:

http://www.mysql.com/

 

Note: Refer to other MySQL related posts for further knowledge.

 

Advanced Concepts:

Introduction:

Videos / Technology Talks:

What & Why?:

Installations & Configurations:

 

MySQL Primer

September 14, 2009 Leave a comment

logo-mysql

  

  

MySQL Origin:

MySQL was originally developed by Michael Widenius and David Axmark beginning in 1994, founders of a Swedish company MySQL AB. It was first released internally on 23 May 1995. Windows version was released on 8 January 1998 for Windows 95 and NT. Sun Microsystems acquired MySQL AB on 26 February 2008. MySQL AB yet holds the copyright to most of the codebase. In April 2009, Oracle Corporation entered into an agreement to purchase Sun Microsystems, current owners of the MySQL intellectual property. The deal was unanimously approved by Sun’s board of directors, and it is anticipated to close soon, subject to Sun stockholder approval, certain regulatory approvals and customary closing conditions.

MySQL is written in C and C++. MySQL runs on more than 20 different system platforms, including AIX, BSDi, FreeBSD, HP-UX, i5/OS, Linux, Mac OS X, NetBSD, Novell NetWare, OpenBSD, OpenSolaris, eComStation, OS/2 Warp, QNX, IRIX, Solaris, Symbian, SunOS, SCO OpenServer, SCO UnixWare, Sanos, Tru64 and Microsoft Windows; giving you the kind of flexibility that puts you in control. In addition, an ODBC interface called MyODBC allows additional programming languages that support the ODBC interface to communicate with a MySQL database, such as ASP or ColdFusion. The MySQL server and official libraries are mostly implemented in ANSI C/ANSI C++.

To administer MySQL databases one can use the included command-line tool (commands: mysql and mysqladmin). Also downloadable from the MySQL site are GUI administration tools: MySQL Administrator, MySQL Migration Toolkit and MySQL Query Browser. The GUI tools are now included in one package called MySQL GUI Tools. In addition to the these tools developed by MySQL AB, there are several other commercial and non-commercial tools available. Examples include Navicat Free Lite Edition, AnySQL Maestro Freeware Edition or SQLyog Community Edition, they are free desktop based GUI tools, and phpMyAdmin, a free Web-based administration interface implemented in PHP. 

  

MySQL Introduction:

MySQL is a relational database management system (RDBMS) which can be used to store, sort, arrange, and display information. MySQL stands for “My Structured Query Language”. The program runs as a server providing multi-user access to a number of database instances. The project’s source code is available under terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL is commonly used by free software projects which require a full-featured database management system, such as WordPress, phpBB etc. It is also used in very high-scale World Wide Web products including Google and Facebook. Several high-traffic web sites (Flickr,Facebook,Wikipedia, Nokia, Auctionmarts and YouTube) use MySQL for data storage and logging of user data.

MySQL is an open-source database system with which we can do the following things:

  • Design the structure of the tables (called schema) and how they relate to one another
  • Add, edit and delete data
  • Sort and manipulate data
  • Query the database (that is, ask questions about the data)
  • Produce listings based on queries

To interact with MySQL, we enter commands on a command line. These commands, such as CREATE, INSERT, UPDATE, etc. are based on a more general language called SQL (Structured Query Language). SQL has a long and colorful history, starting at IBM in the 70’s (based on the research of E.F. Codd who developed the relational data model in 1970), and later through the work of a small company called Relational Software, Inc. In 1979, this group produced the first commercially available relational database system and implemented SQL as its query language. They called the product Oracle.

The MySQL database has become the world’s most popular open source database because of its consistent fast performance, high reliability and ease of use. Not only is MySQL the world’s most popular open source database, it’s also become the database of choice for a new generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python.) MySQL represents the most impressive market success, exceeded only perhaps by Apache, in free and open source software. In terms of installed base, MySQL has left the technically impressive rival PostgreSQL behind. It has marginalized mSQL, SQLite, and SAP DB. It has started to challenge the proprietary database companies on their own turf. Nobody can say why licensing costs for proprietary databases have dropped sharply in recent years, but one suspects that it’s due to MySQL’s competition.

  • MySQL AB claims an installed base of five million systems, the largest of any database engine.
  • The mysql.com domain sees almost as much traffic as ibm.com.
  • Six hundred attendees flocked to the recent MySQl conference.
  • MySQL AB has recently been heavily marketing, its own publishing outlet,MySQL Press.

Many of the applications that a Web developer wants to use can be made easier by the use of a standardized database to store, organize, and access information. MySQL can easily be integrated into Perl programs by using the Perl DBI (DataBase Independent interface) module. DBI is an Application Program Interface (API) that allows Perl to connect to and query a number of SQL databases (among them MySQL, mSQL, PostgreSQL, Oracle, Sybase, and Informix).

  

  

Architecture: Read more…