MySQL Administrator's Bible- P8

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

131
lượt xem 31
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator's Bible- P8: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator's Bible- P8

MySQL Views 8 Note that updating the view presents a logical error. Even if Benjamin had privileges to the base tables, updating the view would generate no warning or error, but produces incorrect results. Changing a View Deﬁnition There are two ways to change a view. One method has already been discussed — using the CREATE OR REPLACE when deﬁning a view. In addition, MySQL has an ALTER VIEW command. ALTER VIEW works much like ALTER TABLE. The SELECT statement that deﬁnes the view must always be included in the ALTER VIEW statement, even if that part of the view deﬁnition is not being modiﬁed. You may have noticed that in the CREATE VIEW statement, four different clauses may come between the words CREATE and VIEW: CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER} ] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] Similarly, the syntax of the ALTER VIEW statement is: ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER} ] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] Only the view’s deﬁner or a user with the SUPER privilege can ALTER a view. Replication and Views In both row- and statement-based replication, MySQL treats a view the same way it treats a base table. In statement-based replication, CREATE VIEW, ALTER VIEW, and DROP VIEW statements are written to the binary log, and thus replicated. In row-based replication, the underlying data is replicated. The replicate-do-table and replicate-ignore-table replication options are applied to views and tables in both statement- and row-based replication, with the following outcomes: 317
Part II Developing with MySQL ■ A view that matches a replicate-do-table pattern will be written to the binary log, and thus replicated. ■ A view that matches a replicate-ignore-table pattern will not be written to the binary log, and thus not be replicated. ■ replicate-do-table and replicate-ignore-table patterns match the object name only. Therefore, those options will only be applied to views matching the pattern — even if the view references a matching table name. Summary In this chapter, we have described: ■ How to create, change, and drop views ■ Invalid SELECT statements in view deﬁnitions ■ Using views to limit ﬁeld and row data for security purposes ■ How views can simplify and abstract queries ■ Performance implications of views ■ Using views as check constraints ■ How to update underlying base tables using an updatable view ■ Reasons a view may not be updatable ■ Logical errors that may occur when updating data using an updatable view ■ How replication handles views 318
Transactions in MySQL W hen reading about relational database management systems (RDBMSs), you will see the terms transaction and ACID IN THIS CHAPTER compliance. A transaction is a set of SQL statements that are Understanding ACID executed as if they were one statement. For a transaction to be ﬁnished compliance and save data changes permanently, all the statements in the transaction have to be completed. If a transaction is not completed for any reason, Using transactional statements the changes to the dataset that the transaction already made are removed, Using isolation levels placing the database in its original state before the transaction began. Explaining locking and A transaction is a transaction only if it is ACID-compliant. ACID is an deadlocks acronym that stands for atomicity, consistency, isolation, and durability. A proper implementation of these properties guarantees reliable processing of Recovering MySQL transactions database transactions. The properties of ACID are explained in detail in the next section. To begin understanding what transactions are and why they are important, it will be helpful to walk through an example of how transactions are used. The classic transaction example is the database system used by a bank. Consider the following situation: Ziesel wants to move $1,000 from her checking account to the checking account of her neighbor Hudson, who is selling Ziesel his car. If Ziesel has an account id of 145356 and Hudson has an account id of 118254, the following two SQL statements might be used to accomplish this bank transaction: UPDATE checking SET balance = balance - 1000 WHERE id = 145356; UPDATE checking SET balance = balance + 1000 WHERE id = 118254; 319
Part II Developing with MySQL The inherent problem with this is that it takes two statements to accomplish the goal of moving the money from one account to another. What happens if the server experiences a system failure between execution of the ﬁrst and second statement? The $1000 is lost somewhere — poor Ziesel is left with $1000 less in her checking account and Hudson has the same amount of money as when things started. To keep this situation from happening, a transactional wrapper is used. This tells the database server that unless all the statements are completed the results of the statements should not be saved to the database. To do this: START TRANSACTION; UPDATE checking SET balance = balance - 1000 WHERE id = 145356; UPDATE savings SET balance = balance + 1000 WHERE id = 118254; COMMIT; The START TRANSACTION and COMMIT statements are wrapped around the statements to denote the beginning and end of a transaction. In MySQL, transactions can start with START TRANSAC- TION, and transactions are ended with COMMIT (successful transaction) or ROLLBACK (indicating the data should be reset back to the original). See the next section, ‘‘Using Transactional State- ments,’’ for more information. Revisiting the scenario of the server experiencing a system failure while executing the second UPDATE statement, this time after the server starts up it will realize the transaction was not ﬁnished and issue a ROLLBACK, which resets any changes left unﬁnished that were made before system failure. Wrapping both statements in a transaction means that either both statements happen, or neither statement happens. If there is a failure, Ziesel gets her money back and the transaction can be executed again. mysqld supports multiple storage engines. Storage engines are the server components that are used to create and support database tables. These storage engines have dif- ferent characteristics. Some of them do not support transactions, including the MyISAM storage engine. However, there are storage engines that do support transactions, including the InnoDB and Falcon storage engines. There is more information about storage engines in Chapter 11, ‘‘Stor- age Engines.’’ Understanding ACID Compliance As previously mentioned, ACID compliance is an acronym for the characteristics of a transac- tionally safe database. Enforcing atomicity, consistency, isolation, and durability is what ensures that a series of statements is indeed a transaction. That is, it ensures that a series of statements either completes with the resulting changes reliably stored, or, if interrupted, the set of state- ments is rolled back to where it began so the database is not changed. 320
Transactions in MySQL 9 Atomicity Atomicity refers to concept that either all of the statements inside a transaction are completed, or none of them are performed. As with the previous example, in a banking system, the transfer of funds can be completed or it could fail. However, the transfer of funds is not allowed to fail leaving the work half-done. The atomicity property guarantees that one account will not be debited unless the other account is credited. Each transaction is said to be atomic (indivisible) — even though there are actually two statements, they act as if they are one statement. If any part of the transaction fails, the entire transaction must fail. Consistency The consistency property ensures that the database moves from one consistent state to another. If the server were to fail while executing the transfer of money from Ziesel’s account to Hud- son’s account, the database would be left in an inconsistent state. The only way to resolve this inconsistency is to undo changes already made. Before a transaction begins the database should be in a consistent state. Once the transaction either completes successfully or is rolled back the database should still be in a consistent state. In a transactional system (ACID-compliant by deﬁnition), if one or more statements in the transaction do not succeed, the entire transaction must be rolled back to a consistent state. If a transaction is successful, the database moves from one consistent state to another. Isolation The isolation property speciﬁes that data being modiﬁed for one transaction cannot be viewed or modiﬁed by a second transaction until the completion of the ﬁrst transaction. This is impor- tant to support concurrent execution of queries, which is critical in any modern database sys- tem. With isolation, separate transactions can run at the same time without compromising the consistency of the data. In MySQL, the level of isolation that transactions have can be conﬁg- ured. The meanings of different isolation levels and how to set the isolation level are discussed in more detail in the section ‘‘Using Isolation Levels’’ later in this chapter. Durability Durability describes the principle that once a transaction has been successfully completed, the results are recorded by the database server permanently. After this point the transaction is complete, and the data changes must survive even if the database or operating system fails. This is important, because modern-day operating systems do not always perform operations imme- diately. If there is an operating system failure between the time that a transaction successfully completes and the time the data is actually written to the disk permanently, the database has marked the transaction as complete but the data has not been changed appropriately. 321
Part II Developing with MySQL Many databases implement durability by writing complete transactions into a log that can be played back to re-create the database state right before a failure. A transaction is considered successfully committed only after it has been written to this log, called a redo log. Using Transactional Statements Now that you have some theory under your belt it is time to see how you actually work with transactions in MySQL. It has been touched on previously, but it is important to understand that with MySQL you have a choice of the type of storage engine to use for each table. To utilize transactions the storage engine used for each table involved in the transaction must support transactions. Failure to do so will lead to inconsistent results. Five SQL commands are used to work with transactions. We cover each of these in the following subsections. BEGIN, BEGIN WORK, and START TRANSACTION BEGIN, BEGIN WORK, and START TRANSACTION all can be used to start a transaction. Techni- cally, both BEGIN and BEGIN WORK are aliases of the START TRANSACTION command. If you execute any of these statements, it causes an implicit COMMIT of any previous pending transactions that are open by the client thread. In addition, if you had previously executed a LOCK TABLES statement the tables will be unlocked just as though you had issued an UNLOCK TABLES statement. COMMIT The COMMIT statement is used to signify the end of a transaction. At this point all changes to the tables are considered to be durable and will survive server failure. ROLLBACK ROLLBACK is used to roll back a transaction to either the state it was in before execution of the transaction or to a certain point prior to where execution is currently occurring. This point is called the SAVEPOINT. Some statements cannot be rolled back, because they perform an implicit COMMIT when they complete. These include DDL (Data Deﬁnition Lan- guage) statements like CREATE DATABASE, CREATE TABLE, DROP DATABASE, DROP TABLE, and ALTER TABLE. If you have issued a DDL statement early in a transaction and another statement fails, you cannot roll back the transaction by issuing the ROLL- BACK statement. Many of these statements also perform an implicit COMMIT when they begin, too. For the full, up-to-date list of statements that perform an implicit COMMIT, see http://dev.mysql.com/doc/refman/6.0/en/implicit-commit.html. 322
Transactions in MySQL 9 Savepoints A savepoint is a deﬁned point in a transaction. The SAVEPOINT statement is used to set a save- point with a name. The ROLLBACK TO SAVEPOINT statement is used to roll back the transac- tion to the named savepoint speciﬁed. Instead of rolling back all the changes in the transaction, ROLLBACK TO SAVEPOINT savepointname rolls back modiﬁcations to rows made in the cur- rent transaction after the savepoint at savepointname. The data is in the same state it was in at the time the savepoint was reached by the transaction. To remove the named savepoint from the set of deﬁned savepoints in the current transaction, use the RELEASE SAVEPOINT command. Here is an example showing the use of SAVEPOINT and RELEASE SAVEPOINT: mysql> use test; Database changed mysql> DROP TABLE IF EXISTS trans_test; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE trans_test (id INT PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(8)) ENGINE=InnoDB; Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO trans_test (name) VALUES (’a’), (’b’); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT id,name FROM trans_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE trans_test SET name=’z’ WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SAVEPOINT savepoint_one; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE trans_test SET name=’y’ WHERE id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 323
Part II Developing with MySQL mysql> SELECT id,name FROM trans_test; +----+------+ | id | name | +----+------+ | 1 | z | | 2 | y | +----+------+ 2 rows in set (0.00 sec) mysql> ROLLBACK TO SAVEPOINT savepoint_one; Query OK, 0 rows affected (0.00 sec) mysql> SELECT id,name FROM trans_test; +----+------+ | id | name | +----+------+ | 1 | z | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.03 sec) Notice that the ﬁrst row still has a NAME ﬁeld value of z, whereas the last row, whose NAME ﬁeld value was changed to y, has reverted to l. AUTOCOMMIT The use of the AUTOCOMMIT statement is another way to work with transactions rather than the more traditional START TRANSACTION or BEGIN. To a transactional storage engine such as InnoDB or Falcon every statement is considered a transactional state- ment in itself if AUTOCOMMIT is enabled (AUTOCOMMIT = 1). The result of this is that if AUTOCOMMIT is enabled, which it is by default, mysqld wraps every statement with START TRANSACTION and COMMIT statements. However, if you explicitly start a transaction with START TRANSACTION, a new transaction is started and AUTOCOMMIT is off for the new transaction. After execution of a transaction using the START TRANSACTION and COMMIT commands mysqld reverts to the autocommit mode it was in before the transaction began. This can cause unpredictable and undesirable results. From our previous example of Ziesel and Hudson at the bank, here are the SQL statements we initially used to update the two accounts: UPDATE checking SET balance = balance - 1000 WHERE id = 145356; UPDATE checking SET balance = balance + 1000 WHERE id = 118254; 324
Transactions in MySQL 9 If you have AUTOCOMMIT enabled (which it is by default), here is what mysqld actually executes: START TRANSACTION; UPDATE checking SET balance = balance - 1000 WHERE id = 145356; COMMIT; START TRANSACTION; UPDATE checking SET balance = balance + 1000 WHERE id = 118254; COMMIT; This ensures that each modiﬁcation is actually committed to the database, performing everything that happens on COMMIT, including issuing a disk ﬂush if the storage engine is conﬁgured to do so. Disabling AUTOCOMMIT (using SET AUTOCOMMIT=0) is equivalent to executing a START TRANSACTION statement for every statement in the session. You now have a transaction open that will not be closed until a COMMIT or ROLLBACK is issued. mysql> SELECT id,name FROM trans_test; +----+------+ | id | name | +----+------+ | 1 | z | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE trans_test SET name=’a’ WHERE id=’1’; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> COMMIT; Query OK, 0 rows affected (0.06 sec) As you can see the SET AUTOCOMMIT=0 statement works just as a BEGIN or START TRANSACTION statement. After the COMMIT, a new transaction is started without needing to use START TRANSACTION, BEGIN, or BEGIN WORK. Using Isolation Levels Isolation levels determine how data is isolated among different transactions. If you begin a transaction, read a row from a table, change the row, read the same row from the table, and then commit, what do you see at each step? What does another transaction reading the 325
Part II Developing with MySQL same row see at each step? The exact nature of what is isolated and under what conditions is determined by the isolation level. In MySQL, the server can be set to a particular isolation level, and connections can also set their isolation level, overriding the global isolation level. For the purpose of deﬁning isolation levels, we will describe the behavior when all connections have the same isolation level as the server. After describing each level, we go into detailed examples of each isolation level. MySQL supports the four standard isolation levels: ■ READ UNCOMMITTED — This setting allows all transactions to see all uncommitted changes, whether within their own transaction or in another transaction. These are called dirty reads — the data is said to be dirty because the change is not permanent yet. Queries inside one transaction are affected by uncommitted changes in another transaction. This is not desirable, and in fact violates ACID compliance. Setting the isolation level to READ UNCOMMITTED gets rid of transaction support — though you can still wrap transactions with START TRANSACTION, ROLLBACK, and COMMIT, there is no isolation and thus your system is no longer transactional. ■ READ COMMITTED — Only data changed by committed transactions are visible to other transactions. However, within a transaction, dirty data can still be read. This means iden- tical queries within a transaction can return differing results. For example, if a transaction reads a row, changes the row, and reads the row again, both reads will produce different data, because the data was changed inside the transaction. Other connections will see only the unchanged data, until the transaction is committed. READ COMMITTED is the default isolation level for SQL Server and Oracle. ■ REPEATABLE READ — The default isolation level for MySQL is REPEATABLE READ. At the time of this writing, only the Falcon and InnoDB storage engines are transactional, so changing isolation levels only applies to transactions that query tables that use Falcon or InnoDB. Using the REPEATABLE READ isolation level, all reads within a transaction show the same data values, even if a second transaction has committed a data change while the ﬁrst transaction was still running. If a transaction starts, reads a row, waits 60 seconds, and reads the same row again, both data reads will be the same — even if in those 60 seconds another transaction has changed and committed data. The ﬁrst transaction has the same data when it repeats the read. Any transactions started after the data commit will see the new data. REPEATABLE READ may not seem like a good idea — after all, if the data changes, shouldn’t a transaction be aware of that? The problem is that a transaction may take different actions based on the values of the data. Data values changing in the middle of a transaction may lead to unexpected consequences. Consider what would happen if the schema changed in the middle of a transaction, and the desired ﬁelds or tables cease to exist! ■ SERIALIZABLE — In the SERIALIZABLE isolation level of MySQL, data reads are implicitly run with a read lock (the LOCK IN SHARE MODE clause; see the example in the 326
Transactions in MySQL 9 ‘‘Row-Level Locks’’ section later in this chapter). Other transactions can read the data, but no transactions can update this data until all the read locks are released. READ UNCOMMITTED is the easiest isolation level to implement and provides the fastest per- formance. The problem with READ UNCOMMITTED is that it provides no isolation between transactions. READ COMMITTED provides some isolation and slightly slower performance, because only committed data changes are seen by other transactions. However, READ COMMITTED does not address the issue of data changing in the middle of a transaction. REPEATABLE READ provides more isolation from a transaction, ensuring that data reads are the same throughout the transaction even if the data has been changed and committed by a different transaction. The SERIALIZABLE isolation level provides the slowest performance but also the most isolation between transactions. The mysqld isolation level is set by the tx_isolation system variable. All connections to the database default to using the isolation level set by the system variable. If the system variable is changed, new connections will use the new isolation level but existing connections continue to use the previous isolation level. In addition, isolation levels can be set per connection. Though the ﬂexibility of this system is nice, it does bring complexity in that you may have a server level setting of READ COMMITTED and a connection with a session setting of READ UNCOMMITTED. The connection with the READ UNCOMMITTED isolation setting will see uncommitted changes, even from connections with uncommitted transactions that changed data using the READ COMMITTED isolation level. In simpler terms, the isolation setting of the connection is the isolation setting used for any transactions within that connection. To determine the isolation level of your connection: mysql> SHOW SESSION VARIABLES LIKE ’tx_isolation’; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | tx_isolation | READ-UNCOMMITTED | +---------------+------------------+ 1 row in set (0.00 sec) mysql> SELECT @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.00 sec) mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE -> FROM INFORMATION_SCHEMA.SESSION_VARIABLES -> WHERE VARIABLE_NAME=’tx_isolation’; 327
Part II Developing with MySQL +---------------+------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+------------------+ | TX_ISOLATION | READ-UNCOMMITTED | +---------------+------------------+ 1 row in set (0.07 sec) To see the isolation level for the server: mysql> SELECT @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) mysql> SHOW SESSION GLOBAL VARIABLES LIKE ’tx%’; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | tx_isolation | REPEATABLE-READ | +---------------+------------------+ 1 row in set (0.00 sec) mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE -> FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -> WHERE VARIABLE_NAME=’tx_isolation’; +---------------+-----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+-----------------+ | TX_ISOLATION | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.07 sec) To change the server (global) or connection (session) isolation level you can execute either of the following SET commands: SET @@{global|session}.tx_isolation= {read-uncommitted| read-committed|repeatable-read|serializable} SET {GLOBAL | SESSION} TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} The transaction_isolation variable can be set in an option ﬁle to change the default tx_isolation level. 328
Transactions in MySQL 9 READ UNCOMMITED Recall that the READ UNCOMMITTED isolation level provides no isolation, allowing dirty reads. To see how READ UNCOMMITTED behaves, open a connection, set the isolation level, and check to make sure it is set appropriately: mysql> SET @@session.tx_isolation=’READ-UNCOMMITTED’; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE ’tx_isolation’; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | tx_isolation | READ-UNCOMMITTED | +---------------+------------------+ 1 row in set (0.00 sec) To make it easier to keep track of connections, change the prompt of this connection to ‘my1> ’ — make sure there is one space after >: mysql> prompt my1> PROMPT set to ’my1> ’ my1> Open a second connection without closing the ﬁrst connection, and set the prompt and isolation level to READ UNCOMMITTED. Return to your ﬁrst connection and perform the following: my1> use test; Database changed my1> CREATE TABLE isolation_test (id INT PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(8)) ENGINE=InnoDB; Query OK, 0 rows affected (0.35 sec) my1> INSERT INTO isolation_test (name) -> VALUES (’a’),(’b’); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 my1> SELECT id,name FROM isolation_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) 329
Part II Developing with MySQL Next execute the following commands in your second connection: my2> use test; Database changed my2> BEGIN; Query OK, 0 rows affected (0.00 sec) my2> INSERT INTO isolation_test (name) VALUES (’c’),(’d’); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Returning to your ﬁrst connection: my1> SELECT id,name FROM isolation_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +----+------+ 4 rows in set (0.00 sec) Notice that the ﬁrst connection can read the uncommitted c and d values of connection two. This is the essence of the READ UNCOMMITTED isolation level. Returning to the second connection: my2> ROLLBACK; Query OK, 0 rows affected (0.01 sec) Now that you have rolled the transaction back in the second connection, return to the ﬁrst: my1> SELECT id,name FROM isolation_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) The ﬁrst connection sees the data rolled back. READ UNCOMMITTED can provide for unpre- dictable results! 330
Transactions in MySQL 9 READ COMMITTED As stated before, an isolation level of READ COMMITTED eliminates dirty reads. To see an example you once again use two connections, setting the isolation level in both to READ COMMITTED. Once they are set and double-checked, do the following in the ﬁrst connection: my1> START TRANSACTION; my1> SELECT id,name FROM isolation_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) In the second connection: my2> BEGIN; Query OK, 0 rows affected (0.00 sec) my2> INSERT INTO isolation_test (name) VALUES (’c’),(’d’); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Returning to your ﬁrst connection: my1> SELECT id,name FROM isolation_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) Notice that this connection does not see the data changes the second connection made — there are no dirty reads. Unfortunately, a second situation called an inconsistent read is possible. An inconsistent read is when a transaction reads data that has changed during transaction execution. Though the changed data was committed by another transaction, a transaction already in progress depends on the data not being changed by another transaction. 331
Part II Developing with MySQL To show this, commit the changes made in the second connection: my2> COMMIT; Query OK, 0 rows affected (0.05 sec) Returning to the ﬁrst connection, check the isolation_test table and you will see the new data: my1> SELECT id,name FROM isolation_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +----+------+ 4 rows in set (0.00 sec) Once the transaction in the second connection was committed the results became visible to the ﬁrst connection, even though the ﬁrst connection was already open and had already read the data once, with the original values. REPEATABLE READ With a REPEATABLE READ isolation level there is no possibility of either dirty or inconsistent reads. To prove this to yourself, repeat the previous example with the tx_isolation level set to REPEATABLE READ for both connections. The REPEATABLE READ isolation level allows a transaction to see the same data for values it has already read regardless of whether or not the data has been changed. Unfortunately, this leads to a problem called a phantom read, because it reads data that no longer exists. As an example, a company generates reports based on customer invoice data. Set your connec- tions to the REPEATABLE READ isolation level, and in the ﬁrst connection populate a table and start a transaction: my1> CREATE TABLE invoice (customer_id INT NOT NULL, -> invoice_date DATE NOT NULL) ENGINE=InnoDB; Query OK, 0 rows affected (0.38 sec) my1> INSERT INTO invoice (customer_id,invoice_date) -> VALUES (1,’2008-10-07’), (1,’2008-11-15’), (2,’2009-01-01’); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 my1> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) 332
Transactions in MySQL 9 my1> SELECT customer_id, invoice_date -> FROM invoice WHERE invoice_date > ’2008-10-01’; +-------------+--------------+ | customer_id | invoice_date | +-------------+--------------+ | 1 | 2008-10-07 | | 1 | 2008-11-15 | | 2 | 2009-01-01 | +-------------+--------------+ 3 rows in set (0.00 sec) The ﬁrst connection represents a data processor — imagine that it processes the data for report- ing. While this is going on, the following happens in a second connection: my2> BEGIN; Query OK, 0 rows affected (0.00 sec) my2> DELETE FROM invoice WHERE invoice_date < ’2009-01-01’; Query OK, 2 rows affected (0.01 sec) my2> COMMIT; Query OK, 0 rows affected (0.00 sec) my2> SELECT customer_id, invoice_date FROM invoice; +-------------+--------------+ | customer_id | invoice_date | +-------------+--------------+ | 2 | 2009-01-01 | +-------------+--------------+ 1 row in set (0.00 sec) In the ﬁrst connection, however, three rows of data appear: my1> SELECT customer_id, invoice_date FROM invoice; +-------------+--------------+ | customer_id | invoice_date | +-------------+--------------+ | 1 | 2008-10-07 | | 1 | 2008-11-15 | | 2 | 2009-01-01 | +-------------+--------------+ 3 rows in set (0.00 sec) The ﬁrst transaction is still seeing the ﬁrst two records, which have already been deleted. It is doing a phantom read of those two records. The phantom read was introduced by trying to ﬁx the inconsistent read problem in READ UNCOMMITTED. However, READ UNCOMMITTED still allows other transactions to change the data. The ideal scenario is one where data read by one transaction cannot be modiﬁed by another at all. This is the SERIALIZABLE isolation level. 333
Part II Developing with MySQL SERIALIZABLE With the SERIALIZABLE isolation level, updates are not permitted by a transaction if another transaction has already read the data. Set both connections to use the SERIALIZABLE isolation level, and in the ﬁrst connection: my1> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) my1> SELECT id,name FROM isolation_test; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +----+------+ 4 rows in set (0.00 sec) In the second connection begin a transaction: my2> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) my2> UPDATE isolation_test SET name=’z’ WHERE id=1; The query in your second connection hangs — it is waiting for the ﬁrst connection to release the implicit read lock it has on the data. If the second connection exceeds the lock wait timeout value, you will see: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction If your second connection has timed out, run the UPDATE again, and COMMIT the transaction in connection one: my1> COMMIT; Query OK, 0 rows affected (0.00 sec) Your second connection should show that the update completed. Note the amount of time required to complete the update in connection two because of this locking: Query OK, 1 row affected (14.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 334
Transactions in MySQL 9 At this point both connections should see the update to the table — the ﬁrst connection has committed, and any new transactions will see the new data. Do not forget to commit your transaction in connection two: my2> COMMIT; Query OK, 0 rows affected (0.09 sec) Although the SERIALIZABLE isolation level provides the most isolation available, it is often both excessive and not efﬁcient. With both the InnoDB and Falcon storage engines techniques are implemented such as next-key locking and multi-version concurrency control (see the next section) that effectively eliminate problems caused by phantom rows. The REPEATABLE READ isolation will provide for better concurrency, so unless there is some special reason, this should be the preferred isolation level. Changing the isolation level is very rare and should only be done very carefully. You can ﬁnd a good article written by one of the book’s authors showing more examples of each isolation level at http://sheeri.com/node/144. Multi-version concurrency control The REPEATABLE READ isolation level can only occur if each transaction has its own virtual view of the data it reads and writes. This is accomplished in InnoDB and Falcon by using multi-version concurrency control (MVCC). MVCC is a versioning system for data, just like there are version control systems for code (cvs, Subversion, Mercurial, and so on). When a transaction starts, you can think of it as check- ing out a copy of the data as it is at the start of the transaction. Note that the mechanics are not the same, but the concept is — in version control systems, an actual local copy of code is made, which would be impossibly unwieldy on a system with many transactions and a large amount of data. Needless to say, MVCC has a very complex job when managing hundreds of concurrent transactions. If a second transaction starts, it ‘‘checks out’’ its own copy of the data. If the ﬁrst transaction makes changes and commits, the second transaction will not see the data. The second trans- action can only work with the data it has. There is no way to update the data that the second transaction sees, though the second transaction could issue a ROLLBACK and start the transaction again to see the new data. The drawback of MVCC is the cost of storing multiple versions of objects in the database. The beneﬁt is that queries are not blocked as they are in the SERIALIZABLE isolation level. MVCC greatly increases the concurrency ability for transactional storage engines. 335
Part II Developing with MySQL Explaining Locking and Deadlocks Locks are used by a thread to keep other threads from modifying data in the area of the database that is locked. Locks are one way to enforce isolation — if a transaction cannot read or write data, it is isolated from other transactions. There are two types of locks — read locks and write locks: ■ Read Lock — The locked data is reserved for reading by the current thread. Other threads can read the locked data, but they cannot change the locked data. A read lock is also called a shared lock, because more than one thread can have a read lock on data. ■ Write Lock — The locked data is reserved for writing by the current thread. Other threads can neither read nor write the locked data. A write lock is also called an exclusive lock, because only one thread at a time can have a write lock on data. There are three levels of locking: ■ Table Lock — The entire table is locked. ■ Page Lock — The lock is set at the memory page level. A page is a group of rows. The exact row count can vary depending on a number of factors but it will typically be a subset of one entire table. ■ Row Lock — The lock is set at the row level. Each row being read or written to is locked. Different storage engines use different levels of locking. We cover each of these locking levels in the next three sections. A deadlock is when two transactions are simultaneously trying to access the same data in the database, and each transaction has locked data that the other transaction needs to continue. Without being able to continue, the transactions will not release their existing lock on the data. More speciﬁcally, one transaction holds a lock on one set of rows and is requesting a lock on another set of rows; those rows are locked by a second transaction that is simultaneously requesting a lock on the rows locked by the ﬁrst transaction. For example, consider the following timeline: 1. Transaction one (T1) requests a write lock on Table B, rows 23 and 78. 2. T1 receives a write lock on Table B, rows 23 and 78. No other transaction can read or write these rows. 3. Transaction two (T2) requests a write lock on Table A, rows 10 and 15. 4. T2 receives a write lock on Table A, rows 10 and 15. No other transaction can read or write these rows. 5. T1 requests a write lock on Table A, rows 10 and 15. 6. T1 waits for the write lock to be granted, which cannot happen until T2 releases its lock. 336