Home arrow MySQL arrow Page 5 - Storage Engine (Table Types)

Benefits and Drawbacks - MySQL

This chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL's storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O'Reilly Media, 2004.)

TABLE OF CONTENTS:
  1. Storage Engine (Table Types)
  2. Locking
  3. Multi-Version Concurrency Control
  4. Transactions
  5. Bene
  6. Deadlocks
  7. Transactions in MySQL
  8. Selecting the Right Engine
  9. Practical Examples
  10. Table Conversions
  11. The Storage Engines
  12. MyISAM Tables
  13. Compressed MyISAM Tables
  14. InnoDB Tables
  15. Heap (In-Memory) Tables
By: O'Reilly Media
Rating: starstarstarstarstar / 55
August 02, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

ACID transactions ensure that banks donít lose your money. By wrapping arbitrarily complex logic into single units of work, the database server takes some of the burden off application developers. The database serverís ACID properties offer guarantees that reduce the need for code guarding against race conditions and handling crash recovery.

The downside of this extra security is that the database server has to do more work. It also means that a database server with ACID transactions will generally require more CPU power, memory, and disk space than one without them. As mentioned earlier, this is where MySQLís modularity comes into play. Because you can decide on a per-table basis if you need ACID transactions or not, you donít need to pay the performance penalty on a table that really wonít benefit from transactions.

Isolation Levels

The previous description of isolation was a bit simplistic. Isolation is more complex than it might first appear because of some peculiar cases that can occur. The SQL standard defines four isolation levels with specific rules for which changes are and arenít visible inside and outside a transaction. Letís look at each isolation level and the type of problems that can occur.

Read uncommitted

In the read uncommitted isolation level, transactions can view the results of uncommitted transactions. At this level, many problems can occur unless you really, really know what you are doing and have a good reason for doing it. Read uncommitted is rarely used in practice. Reading uncommitted data is also known as a dirty read.

Read committed

The default isolation level for most database systems is read committed. It satisfies the simple definition of isolation used earlier. A transaction will see the results only of transactions that were already committed when it began, and its changes wonít be visible to others until itís committed.

However, there are problems that can occur using that definition. To visualize the problems, refer to the sample data for the Stock and StockPrice tables as shown in Tables 2-2 and 2-3.

id

Ticker

Name

1

MSFT

Microsoft

2

EBAY

eBay

3

YHOO

Yahoo!

4

AMZN

Amazon

Table 2-2. The Stock table.

stock_id

date

open

high

low

close

1

2002-05-01

21.25

22.30

20.18

21.30

2

2002-05-01

10.01

10.20

10.01

10.18

3

2002-05-01

18.23

19.12

18.10

19.00

4

2002-05-01

45.55

46.99

44.87

45.71

1

2002-05-02

21.30

21.45

20.02

20.21

2

2002-05-02

10.18

10.55

10.10

10.35

3

2002-05-02

19.01

19.88

19.01

19.22

4

2002-05-02

45.69

45.69

44.03

44.30

Table 2-3. The StockPrice table

Imagine you have a Perl script that runs nightly to fetch price data about your favorite stocks. For each stock, it fetches the data and adds a record to the StockPrice

table with the dayís numbers. So to update the information for Amazon.com, the transaction might look like this:

BEGIN;
SELECT @id := id FROM Stock WHERE ticker = 'AMZN';
INSERT INTO StockPrice VALUES (@id, '2002-05-03', 20.50, 21.10, 20.08, 21.02);
COMMIT;

But what if, between the select and insert, Amazonís id changes from 4 to 17 and a new stock is added with id 4? Or what if Amazon is removed entirely? Youíll end up inserting a record with the wrong id in the first case. And in the second case, youíve inserted a record for which there is no longer a corresponding row in the Stock table. Neither of these is what you intended.

The problem is that you have a nonrepeatable read in the query. That is, the data you read in the SELECT becomes invalid by the time you execute the INSERT. The repeatable read isolation level exists to solve this problem.

Repeatable read

At the repeatable read isolation level, any rows that are read during a transaction are locked so that they canít be changed until the transaction finishes. This provides the perfect solution to the problem mentioned in the previous section, in which Ama-zonís id can change or vanish entirely. However, this isolation level still leaves the door open to another tricky problem: phantom reads.

Using the same data, imagine that you have a script that performs some analysis based on the data in the StockPrice table. And letís assume it does this while the nightly update is also running.

The analysis script does something like this:

BEGIN;
SELECT * FROM StockPrice WHERE close BETWEEN 10 and 20;
// think for a bit
SELECT * FROM StockPrice WHERE close BETWEEN 10 and 20; COMMIT;

But the nightly update script inserts between those two queries new rows that happen to match the close BETWEEN 10 and 20 condition. The second query will find more rows that the first one! These additional rows are known as phantom rows (or simply phantoms). They werenít locked the first time because they didnít exist when the query ran.

Having said all that, we need to point out that this is a bit more academic than you might think. Phantom rows are such a common problem that InnoDBís locking (known as next-key locking) prevents this from happening. Rather than locking only the rows youíve touched in a query, InnoDB actually locks the slot following them in the index structure as well.

Serializable

The highest level of isolation, serializable, solves the phantom read problem by ordering transactions so that they canít conflict. At this level, a lot of timeouts and lock contention may occur, but the needs of your application may bring you to accept the decreased performance in favor of the data stability that results.

Table 2-2 summarizes the various isolation levels and the drawbacks associated with each one. Keep in mind that as you move down the list, youíre sacrificing concurrency and performance for increased safety.

Isolation level

Dirty reads possible

Non-repeatable reads possible

Phantom reads possible

Read uncommitted

Yes

Yes

Yes

Read committed

No

Yes

Yes

Repeatable read

No

No

Yes

Serializable

No

No

No

Table 2-4. ANSI SQL isolation levels

Buy the book!If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: