A transaction allows us to group a set of operations into an atomic unit.
Two concurrent transactions should look to everyone involved like one of the transactions happened in its entirety before the other one started.
Any technique that meets this criterion is called serializable.
An application program declares where it wants a transaction to start and end.
If a transaction contains any step that requires waiting for the user, the operation may be arbitrarily long. Not good.
The trick is to allow operations from two transactions to be interleaved, but still make the two transactions look like they were serialized.
An application program can request a lock on a particular piece of data.
(Normally, application programs do not request explicit locks, rather, they configure the DBMS to lock data according to standard protocols – see ACID in the next notes.)
The size of the ‘piece’ is known as the granularity of the lock. Typically, lock granularity is a single row in a particular table, but it could be larger or smaller depending on the statement.
Locks come in two types:
eXclusive – write locks
Shared – read locks
InnoDB
From http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
A locking read, an UPDATE, or a DELETE generally sets
record locks on every index record that is scanned in the processing of the SQL
statement. It does not matter whether there are WHERE conditions in the statement that would exclude the
row. InnoDB does not
remember the exact WHERE
condition, but only knows which index ranges were scanned.
If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.
Primary key is an index, so it works best when the where clause uses key values (or the field you use in your where clause has an index defined for it).
On updates – from:
When a transaction
updates a row in a table, or locks it with SELECT FOR UPDATE,
InnoDB establishes a list or queue of locks on that row. Similarly, InnoDB
maintains a list of locks on a table for table-level locks transactions hold.
If a second transaction wants to update a row or lock a table already locked by
a prior transaction in an incompatible mode, InnoDB adds a lock request for the
row to the corresponding queue. For a lock to be acquired by a transaction, all
incompatible lock requests previously entered into the lock queue for that row
or table must be removed (the transactions holding or requesting those locks
either commit or rollback).
See example in Figure 6-8
If all transactions obey the “two-phase locking protocol,” then all possible interleaved schedules are serializable.
When transaction A holds a lock transaction B needs and B holds a lock A needs.
A locks tuple 1 for writing causing an X lock on tuple 1
B locks tuple 2 for writing causing an X lock on tuple 2
A attempts to lock tuple 2
B attempts to lock tuple 1
A and B are now deadlocked.
Deadlock is preventable by requiring application programs basically to lock everything they need at the start of a transaction, but this strategy ties up resources for longer than might be necessary.
Deadlock is also curable by detecting it and rolling back one of the transactions involved in the deadlock.
Transactions can be committed if updates were successful or rolled back.
Mark transaction as complete and leave changes in the database.
Remove any changes made by the transaction.
Optimistic locking allows transactions to finish sooner, but some may need to be rolled back.
Pessimistic locking may require waiting, but the transaction will not need to be rolled back. It may still deadlock and the DBMS may roll it back to break the deadlock.
Start Transaction
Do the read portion of the transaction without locking
Acquire update lock.
Issue update command
End Transaction by Committing (if update was successful) or
Rolling back transaction (if update failed)
A Rollback or Commit releases all locks
Start transaction
Acquire locks –in mysql, you can use the ‘lock tables’ command, which locks the entire table, or ‘select … lock in <share | update> mode’
Do updates
Commit transaction