Note on <Beginning Microsoft SQL Server 2012 Programming> - 05

Chapter 14: Transactions and Locks

Transactions

Definition of ACID:

• Atomicity: The transaction is all or nothing.
• Consistency: All constraints and other data integrity rules have been adhered to, and all related objects (data pages and index pages) have been updated completely.
• Isolation: Each transaction is completely isolated from any other transaction. The actions of one transaction cannot be interfered with by the actions of a separate transaction.
• Durability: After a transaction is completed, its effects are permanently in place in the system. The data is safe, in the sense that things such as a power outage or other non-disk system failure does not lead to data that is only half written.

Actually, every SELECT, INSERT, UPDATE, and DELETE statement you issue in SQL Server is part of an implicit transaction. Even if you issue only one statement, that one statement is considered to be a transaction — everything about the statement is executed, or none of it is.

BEGIN TRAN[SACTION] [<transaction name>|<@transaction variable>]
[ WITH MARK [ <'description'> ]]


COMMIT TRAN[SACTION] [<transaction name>|<@transaction variable>]
 
ROLLBACK TRAN[SACTION] [<transaction name>|<save point name>|
<@transaction variable>|<@savepoint variable>]


SAVE TRAN[SACTION] [<save point name>| <@savepoint variable>]

The only exception to going back to the beginning is through the use of what are called save points, you can roll back to the exact spot in the code that you want to just by naming a save point to which you want to roll back. 

Save points are cleared on ROLLBACK — that is, even if you save five save points, once you perform one ROLLBACK they are all gone.

When you executed the BEGIN TRAN statement, you began writing your work into the transaction log but not into the database. 
Starting when you begin a transaction, your changes are isolated from other users. What you're doing is visible to you only, and isn't really done until you COMMIT — although it looks real to you. 
After you execute the UPDATE statement, you can still query the database as if your change was complete, but only you can see that result. Anyone else attempting to look will get results based on their TRANSACTION ISOLATION LEVEL — they could have to wait for your lock to be released, they could see the old value, or if they're daring they could get a dirty read. 
Once you ROLLBACK, the database reverts to a state as if you had never started

SQL Server Log

In the normal operation of your database, most activities that you perform are logged to the transaction log, rather than written directly to the database. A checkpoint is a periodic operation that forces all dirty pages for the database currently in use to be written to disk. Dirty pages are log or data pages that have been modified after they were read into the cache, but the modifications have not yet been written to disk.

Checkpoints can be issued under the following circumstances:

• By a manual statement (using the CHECKPOINT command)
• At normal shutdown of the server (unless the WITH NOWAIT option is used)
• When you change any database option (for example, single user only, dbo only, and so on)
• When the Simple Recovery option is used and the log becomes 70 percent full
• When the amount of data in the log since the last checkpoint (often called the active portion of the log) exceeds the size that the server could recover in the amount of time specified in the recovery interval option

A recovery happens every time that SQL Server starts. SQL Server takes the database file and applies (by writing them out to the physical database file) any committed changes that are in the log since the last checkpoint.

There are just three cases:

• The red box, the transactions are committed before the last checkpoint. There is no need to do anything at recovery.
• The green box, the transactions had not been committed, they will be rolled forward, which means to read all the related pages back into cache, and then use the information in the log to rerun all the statements that you ran in this transaction.
• The blue box, in effect, they are just as if they never happened.

Dirty Read

Non-Repeatable Read

You can prevent this problem in only two ways:

• Create a CHECK constraint and monitor for the 547 Error.
• Set your isolation level to be REPEATABLE READ or SERIALIZABLE.

Author says: setting it to REPEATABLE READ or SERIALIZABLE is going to cause you as many headaches as it solves.

Phantom

The only cure for this is setting your transaction isolation level to SERIALIZABLE.

Lost Update

This is actually not a database level problem.

Locks

There are six lockable resources for SQL Server, and they form a hierarchy. These include, in ascending order of granularity:

• Database
• Table
• Extent
• Page
• Key
• Row or Row Identifier

Lock Modes

The overhead associated with maintaining locks hinders performance, so in order to balance that, when the number of locks reaches a certain threshold, the lock is escalated to the next highest level and the lowerlevel locks do not have to be so tightly managed. The escalation is based on the number of locks rather than the number of users.

Without intent locks, the higher-level objects wouldn't even know that you had the lock at the lower level. Intent locks improve performance, as SQL Server needs to examine intent locks only at the table level (not check every row or page lock on the table) to determine if a transaction can safely lock the entire table.

Let's put schema lock and bulk update lock aside, there are 6 lock modes

• Shared lock: means when you only need to read data, not change anything. it prevent dirty read.
• Exclusive lock: means when you are changing data(update, delete). It is not compatible with any other lock.
• Update lock: a hybrid between shared locks and exclusive locks. There are two distinct stages to an update:
  1. The stage where you are figuring out what meets the WHERE clause criteria (what's going to be updated). This is the part of an UPDATE query that has an update lock.
  2. The stage where, if you actually decide to perform the update, the lock is upgraded to an exclusive lock. Otherwise, the lock is converted to a shared lock.
• Intent shared lock: A shared lock has been, or is going to be, established at some lower point in the hierarchy. Applies only to tables and pages.
• Intent exclusive lock: An exclusive lock has been, or is going to be, established at some lower point in the hierarchy.
• Shared with intent exclusive lock: A shared lock has been, or is about to be, established lower down the object hierarchy, but the intent is to modify data, so it becomes an intent exclusive at some point.

Lock Compatibility

In practice, two things you will get your hand dirty on and - which are tightly related to locks - are Optimizer Hints and Isolation Level:

Optimizer Hints are used for queries, telling SQL Server to escalate a lock to a specific level.

SELECT * FROM Person.Contact WITH (NOLOCK)

Isolation Level only works with transactions. Transactions and locks are inextricably linked. By default, any lock that is data-modification related is, once created, held for the duration of the transaction. There are five isolation levels that you can set:

• READ COMMITTED (the default): For SELECT statements, the locks associated with them are freed as soon as the statement is complete — SQL Server doesn't wait for the end of the transaction. For UPDATE, DELETE, and INSERT, those locks are held for the duration of the transaction.
• READ UNCOMMITTED: SQL Server will not set any locks or honor any locks. Dirty read can happen.
  Two ways to use it:
  1. Setting transaction isolation level to READ UNCOMMITTED
  2. Using NOLOCK optimizer hint in your query
  The advantage to setting the isolation level is that you don't have to use a hint for every table in your query or use it in multiple queries. The advantages to using the NOLOCK optimizer hint are that you can choose which tables to apply the hint to, and you don't need to remember to set the isolation level back to the default for the connection.
• REPEATABLE READ: It does not only prevent dirty reads (the default already does that) but also prevent non-repeatable reads. Its equivalent optimizer hint is REPEATABLEREAD.
• SERIALIZABLE: means that any UPDATE, DELETE, or INSERT (performed by others) to the table or tables used by your transaction must not meet the WHERE clause of any statement in that transaction. It prevents all forms of concurrency issues except for a lost update. Even phantoms are prevented. Its equivalent optimizer hint is SERIALIZABLE or HOLDLOCK.
• SNAPSHOT: Let's skip this one for the time being.

What the author said:

Consistency and concurrency can, in a practical sense, be thought of as polar opposites. Making things highly isolated can prevent other users from getting to the objects they need — that equates to lower concurrency. The reverse is also true — increasing concurrency reduces the consistency of your database.

Use CHECK constraint together with error handling(monitor 547 error) over REPEATABLE READ.

Utilities to inspect lock usage:

There are one sproc sp_who2 and one DMVsys.dm_tran_locks providing the similar information: 

Say, you run a transaction from one connection:

BEGIN TRAN

	UPDATE HumanResources.Department
	SET Name = 'This is a new name'
	WHERE DepartmentID = 11;

-- ROLLBACK

And run another query through a new connection:

SELECT * FROM [HumanResources].[Department]

Now, execute the sproc:

EXEC sp_who2 

From the result, you can see one SELECT query is hold back due to another query.

And now, run another transaction through a new connection to update the same row:

UPDATE HumanResources.Department
SET Name = 'This is a conflicting tran'
WHERE DepartmentID = 11;

And run the query:

SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

Dealing With Deadlocks

SQL Server has its mechanism to handle deadlock:

• Every five seconds SQL Server checks all the current transactions for what locks they are waiting on, but haven't yet been granted. As it does this, it essentially makes a note that the request exists. It then rechecks the status of all open lock requests again and, if one of the previous requests has still not been granted, it recursively checks all open transactions for a circular chain of lock requests.
• If it finds such a chain, then there is a deadlock, it will choose one or more deadlock victims. By default, the transaction that costs the least to rollback is chosen as victim. There is a way to change this behavior.

Follow the Rules to Avoid Deadlocks

• Use your objects in the same order.
• Keep your transactions as short as possible and in one batch.
• Use the lowest transaction isolation level necessary. Like, using a lower isolation level holds shared locks for a shorter duration than using a higher isolation level does, thereby reducing locking contention.
• Do not allow open-ended interruptions (user interactions, batch separations) within the same transaction. Don't ever create locks that will still be open when you begin some form of open-ended process. Like in the lost-update scenario where someone uses update screens instead of display screensto look at data.
• In controlled environments, use bound connections.

Chapter 21: Playing Administrator

Scheduling Jobs

Jobs are a collection of one or more steps, Step is Single processes that will execute or a batch of commands that will run.  A step is a logical unit of work, such as backing up one database or running a T-SQL script to meet a specific need such as rebuilding all your indexes or updating statistics for the query optimizer. Step may or not run, depending your branching rules.

Jobs can be scheduled based on the following criteria:

• Daily, weekly, or monthly basis
• A specific time of the day
• A specific frequency (say, every 10 minutes, or every hour)
• When the CPU becomes idle for a period of time
• When the SQL Server Agent starts
• In response to an alert

In addition to branching you can, depending on what happens, also tell SQL Server to perform the following:

• Provide notification of the success or failure of a job to an operator. You're allowed to send a separate notification for a network message (which would pop up on users' and screens as long as they are logged in), a pager, and an e-mail address to one operator each.
• Write the information to the event log.
• Automatically delete the job (to prevent executing it later and generally "clean up").

Category in job creation panel is just for organizational purpose. If you're feeling especially organized, you can create your own custom job categories from within SSMS.

If you're going to make use of the notification features of the SQL Agent, you must have an operator set up to define the specifics about who is notified:

Logins and Users

Concept definition:

Principals are the basic unit of authentication. A principal can be atomic, like a user, or it can be a group; either way, the principal is the requesting entity. SQL Server can recognize Windows domain users, domain groups, or native SQL Server logins and roles.

Two security mode:

1. Windows Integrated security: SQL Server recognizes Windows domain users and groups as principals.
2. Mixed Mode: In addition to Windows domain users and groups, SQL Server allows you to create native SQL Server logins that don't relate directly to Windows users.

In either case, SQL will create a superuser account named sa ("System Administrator") as a native-mode login. Choosing Mixed Mode will require a password for the sa user, whereas choosing Windows authentication will create the account in a disabled state.

What the author said:

In general, Windows authentication is preferred because: It is more secure. It uses the built-in Kerberos authentication. Managing users and groups is easier. There's only one list (in Active Directory) to maintain. The sa login is disabled. This closes one avenue of attack.

• The user is just a container for a set of permissions.
• A database can have only one owner (whoever creates the database), whose set of permissions has been complete.
• Roles can be created to package up sets of rights to grant as a block to users, or else individual rights can be granted by the dbo.

Tools to manage login in SSMS

In addition, you can make use of two views to inspect that: