?? ch11.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 11 -- Controlling Transactions</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ch10.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch10.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch12.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch12.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 11 -<BR>
Controlling Transactions</FONT></H1>
</CENTER>
<P>You have spent the last 10 days learning virtually everything that you can do
with data within a relational database. For example, you know how to use the SQL
<TT>SELECT</TT> statement to retrieve data from one or more tables based on a number
of conditions supplied by the user. You have also had a chance to use data modification
statements such as <TT>INSERT</TT>, <TT>UPDATE</TT>, and <TT>DELETE</TT>. As of today,
you have become an intermediate-level SQL and database user. If required, you could
build a database with its associated tables, each of which would contain several
fields of different data types. Using proper design techniques, you could leverage
the information contained within this database into a powerful application.
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>If you are a casual user of SQL who occasionally needs to retrieve data from a
database, the topics of the first 10 days provide most of the information you will
need. However, if you intend to (or are currently required to) develop a professional
application using any type of relational database, the advanced topics covered over
the next four days--transaction control, security, embedded SQL programming, and
database procedures--will help you a great deal. We begin with transaction control.
By the end of the day, you will know the following:
<UL>
<LI>The basics of transaction control
<P>
<LI>How to finalize and or cancel a transaction
<P>
<LI>Some of the differences between Sybase and Oracle transactions
</UL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>We used both Personal Oracle7 and
Sybase's SQL Server to generate today's examples. Please see the documentation for
your specific SQL implementation for any minor differences in syntax.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Transaction Control</FONT></H2>
<P>Transaction control, or transaction management, refers to the capability of a
relational database management system to perform database transactions. Transactions
are units of work that must be done in a logical order and successfully as a group
or not at all. The term unit of work means that a transaction has a beginning and
an end. If anything goes wrong during the transaction, the entire unit of work can
be canceled if desired. If everything looks good, the entire unit of work can be
saved to the database.</P>
<P>In the coming months or years you will probably be implementing applications for
multiple users to use across a network. Client/server environments are designed specifically
for this purpose. Traditionally, a server (in this case, a database server) supports
multiple network connections to it. As often happens with technology, this newfound
flexibility adds a new degree of complexity to the environment. Consider the banking
application described in the next few paragraphs.
<H2><FONT COLOR="#000077">The Banking Application</FONT></H2>
<P>You are employed by First Federal Financial Bank to set up an application that
handles checking account transactions that consist of debits and credits to customers'
checking accounts. You have set up a nice database, which has been tested and verified
to work correctly. After calling up your application, you verify that when you take
$20 out of the account, $20 actually disappears from the database. When you add $50.25
to the checking account, this deposit shows up as expected. You proudly announce
to your bosses that the system is ready to go, and several computers are set up in
a local branch to begin work.</P>
<P>Within minutes, you notice a situation that you did not anticipate: As one teller
is depositing a check, another teller is withdrawing money from the same account.
Within minutes, many depositors' balances are incorrect because multiple users are
updating tables simultaneously. Unfortunately, these multiple updates are overwriting
each other. Shortly thereafter, your application is pulled offline for an overhaul.
We will work through this problem with a database called <TT>CHECKING</TT>. Within
this database are two tables, shown in Tables 11.1 and 11.2.
<H4><FONT COLOR="#000077">Table 11.1. The CUSTOMERS table.</FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><B>Name</B></TD>
<TD ALIGN="LEFT"><B>Address</B></TD>
<TD ALIGN="LEFT"><B>City</B></TD>
<TD ALIGN="LEFT"><B>State</B></TD>
<TD ALIGN="LEFT"><B>Zip</B></TD>
<TD ALIGN="LEFT"><B>Customer_ID</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Bill Turner</TD>
<TD ALIGN="LEFT">725 N. Deal Parkway</TD>
<TD ALIGN="LEFT">Washington</TD>
<TD ALIGN="LEFT">DC</TD>
<TD ALIGN="LEFT">20085</TD>
<TD ALIGN="LEFT">1</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">John Keith</TD>
<TD ALIGN="LEFT">1220 Via De Luna Dr.</TD>
<TD ALIGN="LEFT">Jacksonville</TD>
<TD ALIGN="LEFT">FL</TD>
<TD ALIGN="LEFT">33581</TD>
<TD ALIGN="LEFT">2</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Mary Rosenberg</TD>
<TD ALIGN="LEFT">482 Wannamaker Avenue</TD>
<TD ALIGN="LEFT">Williamsburg</TD>
<TD ALIGN="LEFT">VA</TD>
<TD ALIGN="LEFT">23478</TD>
<TD ALIGN="LEFT">3</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">David Blanken</TD>
<TD ALIGN="LEFT">405 N. Davis Highway</TD>
<TD ALIGN="LEFT">Greenville</TD>
<TD ALIGN="LEFT">SC</TD>
<TD ALIGN="LEFT">29652</TD>
<TD ALIGN="LEFT">4</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Rebecca Little</TD>
<TD ALIGN="LEFT">7753 Woods Lane</TD>
<TD ALIGN="LEFT">Houston</TD>
<TD ALIGN="LEFT">TX</TD>
<TD ALIGN="LEFT">38764</TD>
<TD ALIGN="LEFT">5</TD>
</TR>
</TABLE>
<H4><FONT COLOR="#000077">Table 11.2. The BALANCES table.</FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><B>Average_Bal</B></TD>
<TD ALIGN="LEFT"><B>Curr_Bal</B></TD>
<TD ALIGN="LEFT"><B>Account_ID</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">1298.53</TD>
<TD ALIGN="LEFT">854.22</TD>
<TD ALIGN="LEFT">1</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">5427.22</TD>
<TD ALIGN="LEFT">6015.96</TD>
<TD ALIGN="LEFT">2</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">211.25</TD>
<TD ALIGN="LEFT">190.01</TD>
<TD ALIGN="LEFT">3</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">73.79</TD>
<TD ALIGN="LEFT">25.87</TD>
<TD ALIGN="LEFT">4</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">1285.90</TD>
<TD ALIGN="LEFT">1473.75</TD>
<TD ALIGN="LEFT">5</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">1234.56</TD>
<TD ALIGN="LEFT">1543.67</TD>
<TD ALIGN="LEFT">6</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">345.25</TD>
<TD ALIGN="LEFT">348.03</TD>
<TD ALIGN="LEFT">7</TD>
</TR>
</TABLE>
</P>
<P>Assume now that your application program performs a <TT>SELECT</TT> operation
and retrieves the following data for Bill Turner:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">NAME: Bill Turner
ADDRESS: 725 N. Deal Parkway
CITY: Washington
STATE: DC
ZIP: 20085
CUSTOMER_ID: 1
</FONT></PRE>
<P>While this information is being retrieved, another user with a connection to this
database updates Bill Turner's address information:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>UPDATE CUSTOMERS SET Address = "11741 Kingstowne Road"
WHERE Name = "Bill Turner";</B>
</FONT></PRE>
<P>As you can see, the information you retrieved earlier could be invalid if the
update occurred during the middle of your <TT>SELECT</TT>. If your application fired
off a letter to be sent to Mr. Bill Turner, the address it used would be wrong. Obviously,
if the letter has already been sent, you won't be able to change the address. However,
if you had used a transaction, this data change could have been detected, and all
your other operations could have been rolled back.
<H2><FONT COLOR="#000077">Beginning a Transaction</FONT></H2>
<P>Transactions are quite simple to implement. You will examine the syntax used to
perform transactions using the Oracle RDBMS SQL syntax as well as the Sybase SQL
Server SQL syntax.</P>
<P>All database systems that support transactions must have a way to explicitly tell
the system that a transaction is beginning. (Remember that a transaction is a logical
grouping of work that has a beginning and an end.) Using Personal Oracle7, the syntax
looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SET TRANSACTION {READ ONLY | USE ROLLBACK SEGMENT segment}
</FONT></PRE>
<P>The SQL standard specifies that each database's SQL implementation must support
statement-level read consistency; that is, data must stay consistent while one statement
is executing. However, in many situations data must remain valid across a single
unit of work, not just within a single statement. Oracle enables the user to specify
when the transaction will begin by using the <TT>SET TRANSACTION</TT> statement.
If you wanted to examine Bill Turner's information and make sure that the data was
not changed, you could do the following:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET TRANSACTION READ ONLY;</B>
SQL> <B>SELECT * FROM CUSTOMERS
WHERE NAME = 'Bill Turner';
</B>
<B>---Do Other Operations---</B>
SQL> <B>COMMIT;</B>
</FONT></PRE>
<P>We discuss the <TT>COMMIT</TT> statement later today. The <TT>SET TRANSACTION
READ ONLY</TT> option enables you to effectively lock a set of records until the
transaction ends. You can use the <TT>READ ONLY</TT> option with the following commands:</P>
<P><TT>SELECT</TT></P>
<P><TT>LOCK TABLE</TT></P>
<P><TT>SET ROLE</TT></P>
<P><TT>ALTER SESSION</TT></P>
<P><TT>ALTER SYSTEM</TT></P>
<P><BR>
The option <TT>USE ROLLBACK SEGMENT</TT> tells Oracle which database segment to use
for rollback storage space. This option is an Oracle extension to standard SQL syntax.
Consult your Oracle documentation for more information on using segments to maintain
your database.</P>
<P>SQL Server's Transact-SQL language implements the <TT>BEGIN TRANSACTION</TT> command
with the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">begin {transaction | tran} [transaction_name]
</FONT></PRE>
<P>This implementation is a little different from the Oracle implementation. (Sybase
does not allow you to specify the <TT>READ ONLY</TT> option.) However, Sybase does
allow you to give a transaction a name, as long as that transaction is the outermost
of a set of nested transactions.</P>
<P>The following group of statements illustrates the use of nested transactions using
Sybase's Transact-SQL language:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1><B> begin transaction new_account</B>
2> <B>insert CUSTOMERS values ("Izetta Parsons", "1285 Pineapple Highway", "Greenville", "AL" 32854, 6)</B>
3> <B>if exists(select * from CUSTOMERS where Name = "Izetta Parsons")</B>
4> <B>begin</B>
5> <B>begin transaction</B>
6> <B>insert BALANCES values(1250.76, 1431.26, 8)</B>
7> <B>end</B>
8> <B>else</B>
9> <B>rollback transaction</B>
10> <B>if exists(select * from BALANCES where Account_ID = 8)</B>
11> <B>begin</B>
12> <B>begin transaction</B>
13> <B>insert ACCOUNTS values(8, 6)</B>
14> <B>end</B>
15> <B>else</B>
16> <B>rollback transaction</B>
17> <B>if exists (select * from ACCOUNTS where Account_ID = 8 and Customer_ID = 6)</B>
18> <B>commit transaction</B>
19><B> else</B>
20> <B>rollback transaction</B>
21> <B>go</B>
</FONT></PRE>
<P>For now, don't worry about the <TT>ROLLBACK TRANSACTION</TT> and <TT>COMMIT TRANSACTION</TT>
statements. The important aspect of this example is the nested transaction--or a
transaction within a transaction.</P>
<P>Notice that the original transaction (<TT>new_account</TT>) begins on line 1.
After the first insert, you check to make sure the <TT>INSERT</TT> was executed properly.
Another transaction begins on line 5. This transaction within a transaction is termed
a <I>nested transaction</I>.</P>
<P>Other databases support the <TT>AUTOCOMMIT</TT> option. This option can be used
with the <TT>SET</TT> command. For example:</P>
<PRE><FONT COLOR="#0066FF">SET AUTOCOMMIT [ON | OFF]
</FONT></PRE>
<P>By default, the <TT>SET AUTOCOMMIT ON</TT> command is executed at startup. It
tells SQL to automatically commit all statements you execute. If you do not want
these commands to be automatically executed, set the <TT>AUTOCOMMIT</TT> option to
off:</P>
<PRE><FONT COLOR="#0066FF">SET AUTOCOMMIT OFF
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Check your database system's documentation
to determine how you would begin a transaction.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Finishing a Transaction</FONT></H2>
<P>The Oracle syntax to end a transaction is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">COMMIT [WORK]
[ COMMENT 'text'
| FORCE 'text' [, integer] ] ;
</FONT></PRE>
<P>Here is the same command using Sybase syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">COMMIT (TRANSACTION | TRAN | WORK) (TRANSACTION_NAME)
</FONT></PRE>
<P>The <TT>COMMIT</TT> command saves all changes made during a transaction. Executing
a <TT>COMMIT</TT> statement before beginning a transaction ensures that no errors
were made and no previous transactions are left hanging.</P>
<P>The following example verifies that the <TT>COMMIT</TT> command can be used by
itself without receiving an error back from the database system.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>COMMIT;</B>
SQL> <B>SET TRANSACTION READ ONLY;</B>
SQL> <B>SELECT * FROM CUSTOMERS</B>
<B>WHERE NAME = 'Bill Turner';</B>
---Do Other Operations---
SQL> <B>COMMIT;</B>
</FONT></PRE>
<P>An Oracle SQL use of the <TT>COMMIT</TT> statement would look like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET TRANSACTION;</B>
SQL> <B>INSERT INTO CUSTOMERS VALUES</B>
<B>("John MacDowell", "2000 Lake Lunge Road", "Chicago", "IL", 42854, 7);</B>
SQL> <B>COMMIT;</B>
SQL> <B>SELECT * FROM CUSTOMERS;</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">The CUSTOMERS table.</FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><B>Name</B></TD>
<TD ALIGN="LEFT"><B>Address</B></TD>
<TD ALIGN="LEFT"><B>City</B></TD>
<TD ALIGN="LEFT"><B>State</B></TD>
<TD ALIGN="LEFT"><B>Zip</B></TD>
<TD ALIGN="LEFT"><B>Customer_ID</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Bill Turner</TD>
<TD ALIGN="LEFT">725 N. Deal Parkway</TD>
<TD ALIGN="LEFT">Washington</TD>
<TD ALIGN="LEFT">DC</TD>
<TD ALIGN="LEFT">20085</TD>
<TD ALIGN="LEFT">1</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">John Keith</TD>
<TD ALIGN="LEFT">1220 Via De Luna Dr.</TD>
<TD ALIGN="LEFT">Jacksonville</TD>
<TD ALIGN="LEFT">FL</TD>
<TD ALIGN="LEFT">33581</TD>
<TD ALIGN="LEFT">2</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Mary Rosenberg</TD>
<TD ALIGN="LEFT">482 Wannamaker Avenue</TD>
<TD ALIGN="LEFT">Williamsburg</TD>
<TD ALIGN="LEFT">VA</TD>
<TD ALIGN="LEFT">23478</TD>
<TD ALIGN="LEFT">3</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">David Blanken</TD>
<TD ALIGN="LEFT">405 N. Davis Highway</TD>
<TD ALIGN="LEFT">Greenville</TD>
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -