2779B Module 11 - Managing Transactions and Locks

-What are Transactions

-A logical unit of work with the ACID properties

-Atomicity – indivisible all must succeed or none succeed

-Consistency –once trans finishes the data is in a predictable state

-Isolation – one t has no access to another t

-Durability – once a t finishes only another t can alter the state of the system

-Transactions ensure data modifications are processed together.

-Example: transferring money from checking to savings consists of two operations in the same transaction.

-Transactions are made possible by locks. Ooo

-What are locks?

-Two types

-Read lock (allows other transactions to read but not to write)

-Write lock (stops other transaction from reading or writing)

-Deadlocks can occur (two transactions have locks on same resources)

-Locks prevent conflicts

-Ensure that transactions are serialized

-automagic

-Enable concurrent use of data

-Concurrency Control

-Pessimistic locking

-locks data when read in preparation for an update.

-other users are blocked until lock is released.

-use where high contention for data exists

-Optimistic locking (this can be expensive if contention increases)

-Locks data when and update is performed.

-Error received if data was changed since initial read

-use when low contention for data exists.

-Managing Transactions details to go over

-Autocommit transactions

-explicit transactions

-implicit transactions

-the recovery process

-considerations

-restricted statements

Autocommit Transactions

-default mode

-every statement is committed or rolled back when it has completed

-in auto commit mode a compile time error can cause multiple transactions to fail (batch)

Explicit Transactions

-begin transaction

-commit transaction

-rollback transaction

-save transaction (put in a save point which allows you to rollback to that point)

-syntax:

BEGIN TRANSACTION

Do stuff

COMMIT TRANSACTION

-transaction log (as each transaction succeeds the transaction is written to the TL once commited)

Implicit Transactions

-set implicit_transaction on

-you have to put COMMIT or ROLLBACK after all transactions.

The Recovery Process

-cleans up transactions that are at the end of the log

-any transaction that had begun that had not yet committed during system failure will be rolled back

Considerations:

-keep transactions as short as possible

-use caution with certain sql statements

-avoid transactions that require user interaction

-do not browse data during a transaction

-don’t use loops in transactions

-affect the fewest rows possible

-access the fewest rows possible with select statements inside transaction

-Issues with nested transactions

-Allowed, but not recommended

-@@trancount shows nesting level

Restricted Statements:

-Lock Architecture

-concurrency Problems Prevented by locks

-resources that can be locked

-types of locks

-lock compatablility

Concurrency problems prevented by locks

-lost updates

-uncommitted dependancies (dirty read)

one transaction is able to see the intermediate states of another trans

ex: transferring money from checking to savings, the money is removed from checking and another transaction queries both accounts right at that second, it may appear that both accounts are empty.

-inconsistent analysis (non repeatable read)

-Phantom reads

Resources that can be locked

-you can’t actually lock a row but you can lock a row identifier

Types of locks

Basic

-shared (read only)

-exclusive (process is going to change the data)

Special Locks

-Intent

-Update (starts as basic and becomes update, no gap)

-Schema (prevents the schema from being changed)

-Bulk Update

Lock Compatibility

-shared locks are compatible with all locks except exclusive

-exclusive locks aren’t compatible with anything

-update locks are only compatible with a shared lock

-Managing Locks

-session level locking options

-deadlocks

-view locking info

-best practices

Session level Locking Options

Transaction isolation level

-read uncommitted (allows dirty reads)

-read committed (default)

-if allow snapshot is turned on it will not do a shared lock possible dirty reads here

-repeatable read

-maintains the shared lock until the entire transaction is finished

-serializable

-prevents phantom reads

-“aint nothing screwball gonna happen in that session if you have serializable turned on”

Locking timeout

-times out the process that is block and waiting for a resource.

-when the lock time out period expires that transaction is terminated.

-SET LOCK_TIMEOUT

-@@lock_timeout

View Locking info

-Activity Monitor

-sys.dm_tran_locks DynamicManagementView

-EnumLocks

-SS Profiler

-System Monitor

Deadlocks

-How sql server ends a deadlock

-sql server picks a transaction for termination and returns 1205 error number to application

-set the deadlock priority setting to low

-set the lock timeout setting