Required Knowledge
> Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words.
> Identify MySQL data type properties and appropriate usage
> Recognize and use common functions and expressions for all MySQL data types
> Identify and use comment syntax
> Describe and utilize prepared statements
> Describe transactions and transaction isolation levels and the impact they have on database behavior

> Recognize and use common functions and expressions for all MySQL data types

I believe that this one is just something you learn by using the functions available. Though it would probably be good to know several of the numeric functions, such as min(), max(), avg(), rand(), and sum(). For datetime types, adddate(), subdate(), date_add(), date_sub(), date(), and datediff() would be good, as well as the curdate() and curtime() functions. Count() and concat() are common functions as well. For strings knowing how to trim strings are good rtrim(), ltrim(), trim(), and lower(), upper() are helpful.

> Identify and use comment syntax

using the # to indicate a comment from the hash to the end of the line

using two dashes (–) which will create a comment from there to the end of the line. It’s important to note that MySQL requires at least one whitespace or other control character (space, tab, newline, etc) after the double dash – this is different from standard SQL syntax.

using the standard multi-line syntax (/* comments */) as in most other programming languages. Starting comment indicator and closing comment indicator do not need to be on the same line.

> Describe and utilize prepared statements

Prepared statements are great if you want to use the same exact query when only minor details change (i.e. perhaps only the data values you run in the query are the only things that change). Preparing a statement requires the server to only parse the query once and then the client and server can use a different protocol that ends up taking less network traffic between the two. Also note that a prepared statement is only valid in the session that created it (as it is not stored), and if you use auto-reconnect, you may not be notified that your connection was lost and re-connected, but your prepared statements will all have been lost.

For the purpose of the exam, you need to know how to prepare statements using the command line MySQL client.

PREPARE takes two arguments. The first is the name you wish to assign to the statement, and the second is the text of the SQL statement. Inside the text of the SQL statement, you may only have 1 statement (not multiple), and the ? is used to indicate bound variables that you plan to use later on (e.g. SELECT count(*) from countrylanguage WHERE countrycode = ?). Do not quote the ? character even if you want to bind them to string values. If you prepare a statement with the same name as one previously used, the server will delete the old prepared statement and make a new one.

Prepared statements are limited to the following types of statements: SELECT, CREATE TABLE, SET, DO, and SHOW, and those statements that modify data (INSERT, UPDATE, REPLACE, and DELETE).

You run a prepared statement with the EXECUTE command. First, you need to set the variables you wish to bind to in your prepared statement; if you used more than one variable you will need to set those at the same time, and when you execute the statement, you separate the variables with a comma. e.g. for the following simple prepared statement:

> Describe transactions and transaction isolation levels and the impact they have on database behavior

Transactions are logical groupings of statements that the server handles as a single unit. Either all the statements execute successfully or, if an error occurs, any modifications made by those statements are discarded. Systems that can handle transactions are considered ACID comliant:

Atomic – All statements are successful, or they are cancelled as a group

Consistent – If a database is in a consistent state when a transaction begins, it will be left in a consistent state when the transaction ends.

Isolated – Any transaction on the system does not affect any other transactions on the system.

Durable – Changes are not lost when a transaction completes successfully; all modifications are recorded properly.

Starting a transaction

MySQL starts each client with autocommit mode enabled. This means that every statement commits to the database upon completion, and every line is considered a separate transaction. In order to group multiple lines together as a separate transaction, autocommit mode must be disabled. It’s possible to do this in two ways:

Turn off autocommit explicitly from the command line:
SET AUTOCOMMIT = 0;
When using this method, every statement issued becomes a part of the current transaction until you specifically call COMMIT or a ROLLBACK. As expected, a COMMIT writes all modifications to the dabase, and a ROLLBACK discards all modifications. If you have explicitly turned off autocommit mode, then you must turn it back on, otherwise the session will remain in this mode.

Start a transaction explicitly:
START TRANSACTION (this is the standard SQL syntax)
BEGIN; (synonym for START TRANSACTION)
BEGIN WORK; (synonym for START TRANSACTION)
If you use this method, you will need to use START TRANSACTION each time to begin another transaction after you COMMIT

SAVEPOINTS are a method by which you can commit or rollback to a particular spot in the transaction. To create a savepoint, the syntax is SAVEPOINT savepoint_name;

The above allows you to ROLLBACK to a particular spot in your transaction so that you don’t lose any of the modifications that you have made before the savepoint. To ROLLBACK to a particular savepoint, the syntax is
ROLLBACK TO SAVEPOINT savepoint_name;

If you set any SAVEPOINTS after the name of the one you rolled your transaction back to, those will be deleted. If you only rolled back to the last one, only those statements executed after that last SAVEPOINT will be lost.

There are situations where a transaction my end implicitly. There are several statements that can cause an implicit COMMIT of all previous statements and begin a new transaction. Also, if a client connection closes when a transaction has not been committed, the database will ROLLBACK any changes; this will happen whether or not the connection closes normally or abnormally.

Statements that begin a transaction implicitly commits a current transaction, so that means that transactions cannot be nested.

Locking & Locking Modifiers

In MySQL, InnoDB does not set locks to get consistent reads, because it uses multi-versioning to make them unnecessary. InnoDB multi-versioning operates in a default manner that allows each transaction to only see a snapshot of the database when it was first read.

If locking is ever necessary, MySQL will used row-level locking. What this means is that multiple clients can read and modify tables at the same time, but can never modify the same row:

Different clients can read the same rows simultaneously.

Different clients can modify different rows simultaneously.

Different clients cannot modify the same row simultaneously.

Deadlock in MySQL InnoDB can occur; if two transactions lock a row, and then attempt to modify the row locked by the other transaction, then they will be in a deadlock, as neither transaction can continue. InnoDB solves this problem by trying to pick the transaction that has modified the smallest number of rows, and performs a ROLLBACK on that transaction.

InnoDB also supports locking modifiers that can be used with SELECT statements that effectively turn a read into a locking read. One of them is LOCK IN SHARE MODE, which lets other transactions read the selected rows, but does not allow them to update or delete them until your transaction completes (this is similar to the SERIALIZABLE mode described below, which implicitly adds LOCK IN SHARE MODE to all SELECT statements). The other is FOR UPDATE, which locks each row with an exclusive lock meaning that other transactions can neither read nor write to those rows until your transaction has completed.

InnoDB, by default, operates in a mode that allows each transaction only to see the snapshot of the database when it first read the database. This is important because when multiple clients read and write to a database, several problems may occur:

Dirty Reads – This occurs when one transaction reads an uncommited change made by another transaction; this means that if one transaction performs a ROLLBACK any other transactions will not know that this has occurred, and will operate with the bad data from the dirty read.

Non-Repeatable Reads – This occurs when one transaction performs the same retrieval (read) twice and gets different result sets on each query. If one transaction reads a row, and then another transaction updates that row, the first transaction will get a different result set that cannot be repeated; this violates the consistency aspect of ACID.

Phantoms – Similar to Non-Repeatable Reads, a phantom occurs when a new row is inserted (rather than just modified) by another transaction. If one transaction performs a read, and then another transaction inserts a new row, the first transaction will get inconsistent results; the new row is a phantom.

MySQL handles these possibilities for errors with Isolation Levels. These levels determine how visible changes are between various transaction sessions.

READ UNCOMMITTED – This allows any transaction to see changes that have not been committed by other transactions. This allows dirty reads, non-repeatable reads, and phantoms to occur.

READ COMMITTED – This allows any transaction to see changes that have been committed by other transactions. Non-committed changes will remain invisible between transactions. This allows non-repeatable reads, and phantoms to occur.

REPEATABLE READ – This ensures that each transaction will get the same result for every SELECT statement; it does not read committed nor uncommited changes by any other transaction. InnoDB does not allow phantoms to occur at this level. This is the default mode for InnoDB and allows each transaction to work with it’s own snapshot of the database until the transaction ends.

SERIALIZABLE – This isolation level completely removes one transaction from every other. It is different from the above in that it adds the restriction that if a transaction even just reads a row, other transactions cannot modify that row until the first transaction completes.

Setting the isolation levels can be done in an option file, or from the command line. For option files, the syntax is:transaction-isolation = ISOLATION_LEVEL

From the command line, you can set the isolation level either globally, by session, or by transaction: