Transactions - mechanism used to group a set of SQL statements together such that either all or none of the statements succeed
Locking
There are two locking strategies • Database writers must request and receive from the server a write lock to modify data, and database readers must request and receive from the server a read lock to query data. While multiple users can read data simultaneously, only one write lock is given out at a time for each table (or portion thereof), and read requests are blocked until the write lock is released. • Database writers must request and receive from the server a write lock to modify data, but readers do not need any type of lock to query data. Instead, the server ensures that a reader sees a consistent view of the data (the data seems the same even though other users may be making modifications) from the time her query begins until her query has finished. This approach is known as versioning.
Lock Granularities
Table locks: Keep multiple users from modifying data in the same table simultaneously Page locks: Keep multiple users from modifying data on the same page (a page is a segment of memory generally in the range of 2 KB to 16 KB) of a table simultaneously Row locks: Keep multiple users from modifying the same row in a table simultaneously
What Is a Transaction?
Starting a Transaction
Database servers handle transaction creation in one of two ways: • An active transaction is always associated with a database session, so there is no need or method to explicitly begin a transaction. When the current transaction ends, the server automatically begins a new transaction for your session. • Unless you explicitly begin a transaction, individual SQL statements are automatically committed independently of one another. To begin a transaction, you must first issue a command
Of the three servers, Oracle Database takes the first approach, while Microsoft SQL Server and MySQL take the second approach. One of the advantages of Oracle’s approach to transactions is that, even if you are issuing only a single SQL command, you have the ability to roll back the changes if you don’t like the outcome or if you change your mind. Thus, if you forget to add a where clause to your delete statement, you will have the opportunity to undo the damage. With MySQL and SQL Server, however, once you press the Enter key, the changes brought about by your SQL statement will be permanent. Until you explicitly begin a transaction, you are in what is known as auto commit mode, which means that individual statements are automatically committed by the server.
MySQL allows you to disable autocommit mode via the following:
SET AUTOCOMMIT=0
Check autocommit status:
SELECT @@autocommit;Ending a Transaction
Explicitly end your transaction for your changes to become permanent. You do this by way of the commit command, which instructs the server to mark the changes as permanent and release any resources (i.e., page or row locks) used during the transaction.
If you decide that you want to undo all the changes made since starting the transaction, you must issue the rollback command, which instructs the server to return the data to its pre-transaction state. After the rollback has been completed, any resources used by your session are released.
Transaction Savepoints
You may not want to undo all of the work that has transpired. For these situations, you can establish one or more savepoints within a transaction and use them to roll back to a particular location within your transaction rather than rolling all the way back to the start of the transaction.
SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
- Despite the name, nothing is saved when you create a savepoint. You must eventually issue a commit if you want your transaction to be made permanent.
- If you issue a rollback without naming a savepoint, all savepoints within the transaction will be ignored, and the entire transaction will be undone.
Storage Engine
MyISAM A nontransactional engine employing table locking MEMORY A nontransactional engine used for in-memory tables CSV A transactional engine that stores data in comma-separated files InnoDB A transactional engine employing row-level locking Merge A specialty engine used to make multiple identical MyISAM tables appear as a single table (a.k.a. table partitioning) Archive A specialty engine used to store large amounts of unindexed data, mainly for archival purposes
Change Storage Engine
ALTER TABLE customer ENGINE = INNODB;