Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 16

Programming SQL Server


On Day 15 you looked at views, triggers, and stored procedures. Using these database objects, you are able to use SQL to provide miniature programs. While stored procedures and triggers are very powerful, you can make them even more powerful by combining standard SQL with the advanced programming features of Transact-SQL.

Today's lesson focuses on the programming features of the Transact-SQL language. You start off with a discussion of batches and scripts. You then examine the different types of locking used in SQL Server for concurrency control. Finally, you finish this day with a discussion on implementing transactions.

Batches

A batch is a set of Transact-SQL statements that are interpreted together by SQL Server. They are submitted together, and the end of the batch is detected by usage of the keyword GO. Here is an example of a batch run from ISQL/w.

SELECT au_id, au_lname FROM authors
SELECT pub_id, pub_name FROM publishers
INSERT publishers VALUES (`9998','SAMS Publishing', 'Seattle', 'WA','USA')
GO

Batches follow several rules. All of the SQL statements are compiled together. If there is a syntax error anywhere in the batch, the entire batch is canceled. If you were to modify the previous set of SQL statements and introduce a syntax error, you would get an error message from SQL Server, and none of the statements would run. For example:

SELECT au_id, au_lname FROM authors
SELECT pub_id, pub_name FROM publishers
INSERT notable VALUES (`9998','SAMS Publishing', 'Seattle', 'WA','USA')
GO
Msg 208, Level 16, State 1
Invalid object name `notable'.

ANALYSIS: The error message occurred from the INSERT statement. There is no such database object named notable.

Some statements can be combined in a batch, while other statements are restricted.

The following CREATE statements can be bound together within a single batch.

These CREATE statements cannot be combined with others.

If you attempt to combine them, you will receive error 127, which looks like

This CREATE may only contain 1 statement.


WARNING: Caution should be exercised when using these statements. Although the CREATE RULE, CREATE DEFAULT, and CREATE VIEW statements return the error message 127, CREATE PROC and CREATE TRIGGER merely append any SQL statements as part of the object until the keyword GO. For instance,
	   CREATE DEFAULT t1_default
	   AS
	   `UNKNOWN'
	   SELECT * FROM authors
	   GO

returns the expected error message. The following statement creates a single stored procedure, without any further messages from SQL Server.

	   CREATE PROC myproc
	   AS
	   SELECT * FROM authors
	   RETURN
	   SELECT * FROM publishers
	   GO



There are some additional rules for batches.

Now look at a couple of examples that show these rules.

EXEC sp_bindrule `myrule', `table1.col1'
INSERT table1 values (1,'abcde')
GO

You will get the following error message.

Rule bound to table column.
Msg 225, Level 16, State 1
Cannot run query--referenced object (name NAME NOT RECOVERABLE) 
dropped during query optimization.

This is SQL Server's way of informing you that you've broken the rules for batches.


WARNING: A CREATE TABLE with a check constraint can be in a batch, but if you attempt to insert data that would require checking, the constraint is not enforced. This could lead to data integrity problems, and you should use caution here.
CREATE TABLE table2
(
col1 INT NOT NULL
CONSTRAINT table2_ck1 CHECK (col1 < 100), col2 CHAR(5) NOT NULL
)
INSERT table2 VALUES (102,'abcde')
SELECT * FROM t2
col1    	   col2
----------    ----
102    		   abcde
(1 row(s) affected)

ANALYSIS: The data violates the check constraint, but was inserted anyway. An insert in a new batch will produce the expected error, however.

For example, the following statements would fail with the error that table2 already exists in the database.

DROP TABLE table2
CREATE TABLE table2
(col1 INT NOT NULL)

Scripts

A script is simply a set of one or more batches. Scripts typically are executed as part of some unit of work that needs to be accomplished, such as a data load or database maintenance.

Here's an example of a script.

SELECT au_id, au_lname FROM authors
SELECT pub_id, pub_name FROM publishers
INSERT publishers VALUES (`9997','SAMS Publishing', 'Seattle', 'WA','USA')
GO
SELECT * FROM stores
GO
DELETE publishers WHERE pub_id = `9997'
GO

Note that batches and scripts don't necessarily have anything to do with transactions, which will be discussed shortly. Microsoft ships a variety of scripts you can use as examples. They are located in the \INSTALL directory wherever you have SQL Server installed. You should look for files that end in .SQL. You should see a list similar to the following one. These scripts are excellent examples of how you should do your Transact-SQL scripting.

ADMIN60.SQL CHECKOBJ.SQL CHK_MINS.SQL CONFIG.SQL
CONFIGUR.SQL DROPALL.SQL HELPSQL.SQL INSTCAT.SQL
INSTDIST.SQL INSTLANG.SQL INSTMSDB.SQL INSTPUBS.SQL
INSTREPL.SQL INSTSUPL.SQL MESSAGES.SQL OBJECT60.SQL
ODSOLE.SQL PROCSYST.SQL SERVMSGS.SQL SQLOLE42.SQL
SQLOLE65.SQL TEMPSP.SQL U_TABLES.SQL UNADMIN.SQL
UNOBJ.SQL UPGRADE1.SQL WEB.SQL XPSTAR.SQL

Transactions and Locking

A transaction is a unit of work. Transactions are constantly being used, but you may not be aware of them. For instance, what if a bank was to transfer $50 from a checking account to a savings account, but forgot to put the money into savings? Most people would be pretty upset by that. They expect that if the money came OUT of checking, it will go INTO savings. That's a transaction. The unit of work will complete all commands successfully, or it fails and undoes everything that it has done. You have come to expect transactions in your daily life, but as a SQL Server developer you must manually program transactions for them to work properly. As an administrator, you will need to understand transactions because they can cause your transaction logs to fill up if they are used improperly.

Transactions meet four properties, which when put together are called the ACID properties.

Transactions guarantee that the work being performed can succeed or fail completely, as previously described. Locks provide part of that guarantee. During a transaction, no other transaction can modify data your transaction has changed until you have decided whether the change is permanent or not. While you are modifying the data, you hold an exclusive lock on it. Conversely, you cannot read another transaction's data if it's in the process of modifying that data. You are requesting a shared lock on the other data, but the other transaction is using an exclusive lock on its data, which prevents you from reading it. You will examine locks in more detail later.

Transactions

There are two types of transactions: explicit and implicit. You should examine explicit transactions first.

Explicit transactions are transactions that are manually configured by you. Reserved words are used to indicate the beginning and end of explicit transactions. These reserved words include BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, and SAVE TRANSACTION.

Explicit Transactions

To begin an explicit transaction, you would type the keywords BEGIN TRAN (or BEGIN TRANSACTION if you're in the mood to type more). To indicate to SQL Server that your transaction is complete and all work should be saved, you would enter the COMMIT TRAN statement. Hence, a typical transaction might look like

BEGIN TRAN
    UPDATE authors
    SET city = `San Jose' WHERE name = `Smith'
    INSERT titles
    VALUES (`BU1122',
    'Teach Yourself SQL Server in 21 days','business','9998',$35.00,
    $1000.00,10,4501, `A great book!')
    SELECT * from titleauthor
COMMIT TRAN

You will also have need to cancel transactions. To do this, you would use the ROLLBACK TRAN command. Here's an example of the ROLLBACK TRAN statement.

BEGIN TRAN
    Delete titles where type = `business'
    IF @@ERROR > 0
        ROLLBACK TRAN
    ELSE
    COMMIT TRAN

The ROLLBACK TRAN statement will cancel the transaction completely. Any work that was done in the transaction up to that point will be rolled back, or canceled. You also have the ability to create save points within a transaction, and then selectively roll back to those points. Again, a code example illustrates this best.

BEGIN TRAN
    UPDATE table1 SET col1 = 5 WHERE col2 = 14
    SAVE TRAN savepoint1
    INSERT table2 values (1401,'book review','a1201',9900)
    IF @@error > 0
        ROLLBACK TRAN savepoint1
        DELETE table3 WHERE col1 > 1401
    IF @@error > 0
        ROLLBACK TRAN
    ELSE
    COMMIT TRAN

Notice that the SAVE TRAN command has a name after it, known as the savepoint name. That way, during the first rollback, you can identify that rather than roll back the entire transaction from the beginning, you would like to roll back to a particular named point, in this case savepoint1. The INSERT into table2 would be canceled if the first rollback was issued, but the transaction itself would continue. Essentially the INSERT would be removed from the transaction. Upon the later rollback, since there is no name given to roll back to, ROLLBACK TRAN will go all the way back to the BEGIN TRAN statement.

Now take a closer look at the TRANSACTION statements.

BEGIN TRANsaction [transaction_name]
COMMIT TRANsaction [transaction_name]

transaction_name is an optional name you can assign to the transaction. It is not necessary to name a transaction, and it is recommended that you do not name any transaction except the outermost BEGIN statement in your transaction. The transaction_name must be a valid SQL Server identifier.

SAVE TRANsaction savepoint_name

savepoint_name is a placeholder used to indicate a safe point to abort some amount of work in a transaction, without canceling the entire transaction. The savepoint_name must be a valid SQL Server identifier.

ROLLBACK TRANsaction [transaction_name | savepoint_name]

Refer to the previous comments for BEGIN/COMMIT TRAN and SAVE TRAN statements for a description of the optional names.

As implied in the comments for BEGIN TRAN/COMMIT TRAN in the syntax box, transactions can be nested. However, this nesting is strictly syntactical in nature. Transactions cannot truly be nested. You may have multiple transactions appear to occur within a script, but in fact only one actual transaction is being used.

Here are a few rules about explicit transactions.

Some statements are not allowed as part of explicit transactions. These statements are not allowed because there is no way to roll them back.

The following statements can appear together inside of a transaction (although remember that this does not change any of the rules about batches that were discussed earlier).


NOTE: Be careful about creating objects inside of a transaction. Locks on the system tables will be held for the duration of the transaction and could cause unintended side effects. For instance, other users may be unable to create new objects until the transaction commits or rolls back.

There is no published limit on the number of savepoints within a transaction.

Once committed, there is no way to roll back a transaction.

You may have duplicate savepoint names within a single transaction; however, only the final instance of the savepoint name will actually be used if you roll back to that savepoint. The following code is an example.

BEGIN TRAN
    INSERT
    UPDATE
    SAVE TRAN transave1
    DELETE
    INSERT
    SELECT
    SAVE TRAN transave1
    INSERT
    DELETE
    IF @@ERROR <> 0
         ROLLBACK TRAN transave1
    ELSE
        COMMIT TRAN

In this example the ROLLBACK TRAN statement will only go back as far as the second transave1 savepoint. The first savepoint is ignored once the name is reused.

When you issue a ROLLBACK TRAN statement within a trigger and no savepoint name is specified, the entire transaction is rolled back and the rest of the batch is not executed. However, processing may continue with the next batch.

Calls to remote stored procedures are not normally considered part of a transaction. In the following

BEGIN TRANSACTION
UPDATE table1 SET col1 = 5 WHERE col1 = 1
DELETE table1 WHERE col1 = 5
EXEC server2.pubs..usp_insertpublisher parm1 parm2 parm3
COMMIT TRAN

if the pubs..usp_insertpublisher stored procedure on server2 were to issue a ROLLBACK TRAN statement, it would not affect the local transaction.

Transactions can provide performance benefits. By not writing the transaction log entries to disk until the transaction is completed, SQL Server can provide more efficient utilization of the disks.

Implicit Transactions

Even when it doesn't appear that transactions are being used, they are lurking behind the scenes. Each time you execute any data modification statement in SQL Server, it is an implied transaction.

In the following batch, each SQL statement is a separate transaction. Thus, this batch is actually three separate transactions. If any of the statements fail, it doesn't affect the others. Each statement will succeed or fail on its own, without regard to the other statements in the batch.

INSERT table1 VALUES (1,'abcde')
UPDATE table1 SET col1 = 5 WHERE col1 = 1
DELETE FROM table1 WHERE col1 = 5
GO

How Do Transactions Work?

Now that you have learned about both explicit and implicit transactions, you should look at a step-by-step example of what happens inside of SQL Server during a transaction.

As an example, use the following set of SQL statements.

BEGIN TRAN
    INSERT table1 values (1,'abcde')
    UPDATE table1 SET col1 = 5 WHERE col1 = 1
    DELETE FROM table1 WHERE col1 = 5
COMMIT TRAN

STEP 1:

When the BEGIN TRAN statement is sent to the database, the SQL Server parser detects the request to begin an explicit transaction. SQL Server finds the next available log page in memory and allocates a transaction ID to associate with this new transaction (see Figure 16.1).

Figure 16.1. Step 1 of the transaction process.

STEP 2:

Now, the INSERT statement runs. The new row is recorded in the transaction log, and then the data page for table1 is modified in memory (see Figure 16.2). If the needed page is not in memory, it is retrieved from disk.

STEP 3:

The next statement runs in a similar fashion. The UPDATE statement is recorded in the transaction log, and then the data page is modified in memory as shown in Figure 16.3.

Figure 16.2. Step 2 of the transaction process.

Figure 16.3. Step 3 of the transaction process.


NOTE: In this example, a single row was listed in the log for the update. Most of the time it will probably be true that the log will actually show a delete followed by an insert, rather than a single modify record. It would require an "update in place" to get a single modified record written to the log. There are a variety of conditions required to get an "update in place" to occur. These are enumerated in Day 12, "Data Modification."

STEP 4:

When SQL Server receives the COMMIT TRAN, the log page is written to the log device for the database (see Figure 16.4). This is your guarantee that the transaction can be recovered. Because the log changes are written to disk, it guarantees that the transaction is recoverable, even if power is lost or the database crashes before the data page is written to disk.

Figure 16.4. Step 4 of the transaction process.

This example shows how the log and the data pages are written. You can observe some of this behavior directly by reading the syslogs table in the database of the modification. However, it can be difficult to interpret the data in this table. Here's a sample of what you might see in the transaction log.

SELECT * FROM syslogs
GO
xactid         op
-------------- ---
0x110300001200 0
0x000000000000 17
0x110300001200 9
0x110300001200 9
0x110300001200 9
0x110300001200 9
0x110300001200 30
0x000000000000 17
0x0f0300000700 0
0x0f0300000700 4
0x0f0300000700 9
0x0f0300000700 12
0x0f0300000700 5
0x0f0300000700 30
(14 row(s) affected)

ANALYSIS: The bold face entries are representative of the transaction you looked at above.

The xactid column is the transaction number, and the op column is the kind of operation being performed. A sampling of the op codes is listed in Table 16.1.

Table 16.1. op codes.

Op Code Meaning
0 BEGIN TRAN
4 INSERT
5 DELETE
6 INSIND (INDIRECT INSERT)
7 IINSERT (INDEX INSERT)
8 IDELETE (index delete)
9 MODIFY (update in place)
10 NOOP (change that needs to be done in the future)
11 INOOP (deferred insert)
12 DNOOP (deferred delete)
13 ALLOC (page)
15 EXTENT (allocation)
16 PAGE SPLIT
17 CHECKPOINT
18 SAVEXACT (savepoint)
19 DEALLOC EXTENT
21 DEALLOC PAGE
23 ALLOC EXTENT
24 ALLOC NEW PAGE FOR SPLIT
25 DIRECT CHANGE TO SYSINDEXES
30 COMMIT TRAN

The Checkpoint Process

After the previous discussion, you may be wondering when the data pages are written to disk. The log pages are written when the COMMIT TRAN process occurs. So when is the data written to disk? The answer is in the CHECKPOINT process. The CHECKPOINT process is the internal process SQL Server uses to flush (or copy) the data pages from memory to disk.

The checkpoint helps assure that recovery of committed transactions won't take an excessive amount of time. Once a checkpoint occurs, a log entry is written to indicate that all modified pages in memory have been written to disk. This gives the SQL Server recovery process a point in the log at which it is assured that no earlier committed transactions need to be looked at to guarantee a complete recovery.

There are two kinds of checkpoints in SQL Server: automatic and manual.

The automatic checkpoint process occurs based on internal SQL Server calculations. You configure how often the checkpoint process will occur with the RECOVERY INTERVAL configuration option. This option specifies, in minutes, the maximum amount of time it will take to recover each database in your system. If SQL Server thinks it would take that much time or longer to recover a database, it will issue the automatic checkpoint. When that happens, all modified data pages in memory (for this database) will be written to disk (including log pages). The automatic checkpoint process wakes up every 60 seconds and cycles through each database, determining if the database needs to be checkpointed. Note that for databases with the trunc. log on Chkpt. option set, this process will truncate committed transactions from the log.

A manual checkpoint can be forced at any time by typing the Transact-SQL command CHECKPOINT. Note that you must be a SA or the DBO of a database to execute this command.

When a manual checkpoint is issued, all modified pages in memory are flushed to disk, just as happens during the automatic checkpoint process. Note that a manual checkpoint has no effect on the transaction log (other than copying it to disk), regardless of whether the trunc. log on Chkpt. database option is set.

So why would you want to use a manual checkpoint? Here are three reasons.

Distributed Transactions

All of the transactions discussed so far have been on one and only one server. SQL Server 6.5 has the ability to support transactions that involve more that one server. This capability is supported with the MSDTC (Microsoft Distributed Transaction Coordinator) service.

There are three different ways to use distributed transactions.

Now examine the last two methods more closely. In order to enlist the MSDTC service in your transaction and have it coordinate activity across multiple servers, all you need to do is issue the BEGIN DISTRIBUTED TRANSACTION statement, in exactly the same way you used the BEGIN TRAN statement. In the previous discussion about transactions, it was mentioned that remote stored procedures operate outside of the current transaction from which they are called. With distributed transactions, the remote stored procedures are now included within the transaction. If there is a failure on the remote server, it affects the calling server or servers as well. In the following code example, if there was a failure in the stored procedure called from server2, the transaction on your server would be affected as well.

BEGIN DISTRIBUTED TRANSACTION
UPDATE table1 SET col1 = 5 WHERE col1 = 1
DELETE table1 WHERE col1 = 5
EXEC server2.pubs..usp_insertpublisher parm1 parm2 parm3
COMMIT TRAN

You do not have the ability to run queries remotely, only stored procedures. It's really that simple. Everything else discussed about transactions applies, except that distributed transactions can't be syntactically nested.

The third way mentioned is the SET REMOTE_PROC_TRANSACTIONS statement. When this option is set in a session, all remote stored procedures that are called during the statement are considered part of the transaction. You can also set the same configuration option with sp_configure, and the option will be set for all sessions from that time on. Before setting this as a server-wide configuration option, be sure you have tested the implications of this on any existing remote stored procedures.

Locking

Locking usually comes up in a negative context. Locking helps provide concurrency within the database. Often you'll hear someone talk about locking problems, but rarely will you hear about positive benefits--and there are many. Without locking, SQL Server would have no mechanism to prevent multiple users from updating data at the same time.

In general, there are three types of locks in SQL Server.


NOTE: Update locks are needed when a query goes through two phases to modify data: a search phase and a modify phase. It's possible that if SQL Server used a shared lock during the search phase, another user could also acquire a shared lock on the same object. When the searching transaction went to modify the data, it would need an exclusive lock. The other transaction may have already attempted to get an exclusive lock, and SQL Server would not give you an exclusive lock. Hence, a blocking or deadlock situation might occur. To prevent this, an update lock is used, which prevents another transaction from getting exclusive locks on the object that's been locked for update.

Lock Types

There are also different levels or types of objects that can be locked.

Page-level locking is the default level of locking for most queries. Table-level locking might also occur for operations that require access to an entire table. An example of this type of operation is a query without a where clause. Row-level locking involves locking a single row at a time, instead of a page or table. Row-level locking can be useful because a single page may contain many rows. Generally speaking, the smaller the unit of locking, the better the concurrency (the ability of multiple uses to simultaneously access data).


NOTE: Row-level locking in SQL Server 6.5 only applies to inserts. It is useful when you have what are known as "hotspots" within a table. For instance, if there is not a clustered index on your table, the default behavior is to add all new rows to the end of the table. Even if there is a clustered index, if the index is on an increasing value, the same situation arises (note, however, that only a unique clustered index will use row-level locking). Turning on Insert Row-level locking on these tables will likely improve performance and concurrency. To do so, use the sp_tableoption stored procedure. For example, to turn on row-level locking for the authors table, you could run
	   EXEC sp_tableoption `authors', 'insert row lock', 'true'

The first parameter of the stored procedure is actually treated as a variable with the LIKE condition. You could just as easily have written

	   EXEC sp_tableoption `a%', 'insert row lock', 'true'

This would turn this option on for all tables that begin with the letter a.
Also note that enabling Insert Row-level locking could potentially require that the size of the transaction log be increased.


Controlling Locking

Normally you need not be concerned with controlling locking. For INSERT, UPDATE, and DELETE operations, SQL Server will obtain an exclusive lock. However, SQL Server has the ability to configure locks on a query-by-query basis using locking hints for the SELECT statement. You would specify those after the name of the table in your query. Occasionally, you will have to change the default locking behavior because of problems with transactions conflicting or blocking each other.

For instance, to force an exclusive table lock on the authors table so that no one can modify the table while you examine it, you could run the following query.

SELECT *
FROM authors (TABLOCKX)

There are different parameters that you can in place of TABLOCKX.

Lock Escalation

Lock escalation is the process of changing a lower-level lock (such as a page lock) to a higher-level lock (such as a table lock). SQL Server escalates locks when it determines the need to do so. However, there are several parameters that can be tuned to control lock escalation. These parameters are shown in Figure 16.6 below. To view these configuration options, follow these steps.

1. Right-click your server and choose Configure from the context menu.

2. In the Server Configuration/Options - ServerName dialog box, choose the Configuration tab.

3. Scroll down through the configuration options until you get to Show Advanced Options. Click in the white box and change the existing value to the number 1 (see Figure 16.5).

Figure 16.5. The Show Advanced Options parameter.

4. Click OK when you are finished.

Follow Steps 1 and 2 again. You should now be able to see all of the LE THRESHOLD options shown in Figure 16.6.

Figure 16.6. The Advanced Configuration Options dialog box.

LE Threshold Maximum

The LE threshold maximum parameter controls the maximum number of page locks that will be taken in a single query before SQL Server will escalate to a table lock. If you were to have the LE threshold maximum configured at the default value of 200, it means that when 200 page locks are held by a single query, a table lock of the same type will be acquired, and the individual page locks will be released. This will occur regardless of whether the LE threshold percent has been reached or not. It's probably a good idea to increase this value to improve concurrency.

LE Threshold Minimum

The LE threshold minimum parameter is used when the LE threshold percent parameter has been changed from its default value of 0. Lock escalation to the table level will not occur until this value is reached, even if the the LE threshold percent value has been exceeded. This is to prevent small tables from always escalating to a table lock. The default value of 20 may be too small in many environments.

LE Threshold Percent

The the LE threshold percent parameter is used to control when escalation occurs based upon the size of the table. When the percentage value of this parameter is reached, a table lock will be taken. The default value of 0 means that the LE threshold minimum parameter isn't really used, and escalation to the table level will only occur when the LE threshold maximum is reached.

The LOCKS Option

There is one other configuration option available for locks. That is, suprisingly enough, the LOCKS option. For most installations of SQL Server, the default value of 5000 is sufficient. If you run out of locks, SQL Server will put error messages in both the Windows NT Application Event Log and the SQL Server error log. You can then increase this value. Be aware that each lock requires 60 bytes of storage, so don't increase it without understanding the impact of this (see Day 20, "Performance Tuning and Optimization," for more information about memory configuration).

Observing Locks

To see locks as they occur, you can run the sp_lock or sp_processinfo stored procedures. It may be easier to observe locks using the Microsoft SQL Enterprise Manager | Current Activity Window to view locks. Figure 16.4 is a sample of what that window might look like. Notice that the locks are owned by MS SQLEW (the application name for Enterprise Manager). This is completely normal and nothing to be concerned about.

To see the Current Activity window, click the icon that looks like a bar graph, or from the Server menu, select Current Activity (see Figure 16.7).

Figure 16.7. The Current Activity dialog box.

Deadlocks

A deadlock is a situation in which two transactions conflict with each other and the only resolution is to cancel one of the transactions. An example is the easiest way to understand deadlocks.

Create these two tables, and populate them with data.

CREATE TABLE checking
(acct_num INT NOT NULL,
 last_name CHAR(30) NOT NULL,
 balance MONEY NOT NULL
)
GO
CREATE TABLE savings
(acct_num INT NOT NULL,
 last_name CHAR(30) NOT NULL,
 balance MONEY NOT NULL
)
GO
INSERT checking VALUES (1,'smith', $500.00)
INSERT checking VALUES (2,'Jones', $300.00)
INSERT savings VALUES (1,'smith', $100.00)
INSERT savings VALUES (2,'Jones', $200.00)
GO

Now open 2 isql/w windows. In the first window, run

BEGIN TRAN
    UPDATE checking
    SET balance = balance + $100.00
    WHERE acct_num = 1

You should get back

(1 row(s) affected)

In the second window, run

BEGIN TRAN
    UPDATE savings
    SET balance = balance - $100.00
    WHERE acct_num = 2

with the same results. So far, so good. Now, in the first window, run

UPDATE savings
SET balance = balance - $100.00
WHERE acct_num = 1

Notice that the transaction is still running, as the other query window is blocking it. The results of running sp_lock are shown in the following Output. Notice that for spid 14 it shows the word blk under locktype to indicate that a process is blocking another process, and the other process can't proceed until spid 14 has released its lock on the required resource (in this case, the only page is the savings table).

spid          locktype     table_id      page     dbname
----------    ---------    --------      -----    ------------
13            Ex_table     1456008218       0     pubs
14            Ex_table-blk 1488008332       0     pubs
15            Sh_intent     704005539       0     master
15            Ex_extent             0     336     tempdb
(1 row(s) affected)

In the second window, run

UPDATE checking
SET balance = balance + $100.00
WHERE acct_num = 2

and you should get a message like this:

Msg 1205, Level 13, State 2
Your server command (process id 14) was deadlocked with another process
 and has been chosen as deadlock victim. Re-run your command

Notice that the original query has now completed. Run the following clean-up code in window #1.

COMMIT TRAN
GO
DROP TABLE checking
DROP TABLE savings
GO

Deadlock avoidance is important, because time and resources are wasted when a deadlock occurs. One way to avoid deadlocks is to always access tables in the same order. In the previous example, if both transactions had started with the same table, the deadlock would not have occurred. One of the transactions would have waited for the other to complete before it began. When you do get a deadlock, it is generally a good idea to wait a second or two and then re-submit your transaction.

Transaction Isolation Levels

Transaction isolation levels affect the default kinds and durations of locks that are taken during a SELECT statement. As you've seen above, the types of locks taken can be overridden on a query-by-query basis. The isolation level performs similarly, but can be overridden on a session-level basis (meaning that all queries that run during a single session will have this setting take effect). To do this, use the SET TRANSACTION ISOLATION LEVEL statement.

SET TRANSACTION ISOLATION LEVEL
    {READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}

The default is READ COMMITTED, which means that a SELECT query will only see data that the query can get a shared lock on (it will not do dirty reads). READ UNCOMMITTED is the same as the optimizer hint NOLOCK, and will allow dirty reads on all queries during a particular session. REPEATABLE READ will not release shared locks until the transaction has been completed, and is equivalent to the HOLDLOCK hint previously described. Finally, SERIALIZABLE is implemented exactly the same as REPEATABLE READ.

To view which isolation level is in force, use the DBCC USEROPTIONS command. Without having run the SET TRANSACTION ISOLATION LEVEL statement, you would see

DBCC USEROPTIONS
GO
Set Option    		      Value
--------------------     -------------------------------
textsize                 64512
language                 us_english
dateformat               mdy
datefirst                7
(4 row(s) affected)
DBCC execution completed. If DBCC printed error messages, 
see your System Administrator.

Note the difference after turning on dirty reads with the READ UNCOMMITTED option.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DBCC USEROPTIONS
GO
Set Option               Value
--------------------     -----------------------------------
textsize                 64512
language                 us_english
dateformat               mdy
datefirst                7
isolation level          read uncommitted
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
 see your System Administrator.

Summary

Today you learned about transactions, distributed transactions, locks, batches, and scripts. You have seen how batches are used from ISQL/w. You have looked at several statements that will work together in a batch, as well as some that won't work together. Scripts were also discussed.

You went over the syntax for locks, how to override SQL Server's decision about when to use a particular kind of lock, and when the usage of lock overrides would be appropriate. You have also learned how to observe locks using Transact-SQL and SQL Enterprise Manager. Additionally, you have learned how to create a deadlock situation and how you can avoid them in the future. You have seen how to use transactions (and distributed transactions) to accomplish "units of work" with SQL Server.

Q&A

Q Is this a valid example of a batch?
BEGIN TRAN
    SELECT * FROM titles
    UPDATE titles set PRICE = $12.99 WHERE TITLE_ID = `BU1032'
    ROLLBACK TRAN
    SELECT * FROM titles
COMMIT TRAN
A No. If you were to run the above set of SQL statements, you would end up with the following error message.
Msg 3902, Level 16, State 1
The commit transaction request has no corresponding BEGIN TRANSACTION.
This is because the transaction has been rolled back by the time the COMMIT TRAN is run.

Q True or False? COMMIT TRAN writes all modified pages in memory to disk.


A
False. The CHECKPOINT process writes modified pages from memory to disk devices in SQL Server (both data and log pages). The COMMIT TRAN statement writes the modified log pages for each transaction to disk.

Q What kind of locks are compatible with a shared lock on a page?


A
Shared locks are compatible with shared locks and update locks.

Q What is it called when two transactions have an exclusive lock on a resource, and each of the other transactions needs to lock the resource the other transaction has locked?


A
A deadlock.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you've learned. Try to understand the quiz and exercise answer before continuing on to the next day's lesson. Answers are provided in Appendix B.

Quiz

1. What CREATE statements are allowed within a single batch?

2. What locks will be held at the time of the COMMIT TRAN from the following batch?
BEGIN TRAN
    UPDATE authors SET au_lname = `Johnson' WHERE au_lname = `Smith'
    INSERT publishers VALUES (`9991','SAMS','Indianapolis','IN','USA')
    SELECT * FROM publishers (HOLDLOCK)
COMMIT TRAN
3. How would I enable remote-stored procedures to automatically be part of a distributed transaction at all times?

Exercise

You have added a new book to your inventory (How to Surf the Net in 3 Easy Steps). Not only is the title new, but so is the publisher (waycool publishers) and the two authors (Ann Jackson and Bob Greene). Write a script to add all of this information so that it all completes or fails together.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.