One of the main benefits of using SQL Server is its ability to handle transactions. This benefit comes with a learning curve, as SQL Server handles transactions differently than VFP does. SQL Server provides a much greater level of control over how transactions occur. As usual, having more control means there is more to know. This chapter covers the details of SQL Server’s transaction handling as well as how to design and write Visual FoxPro code to manage transactions properly.
Transaction basics
A transaction is a sequence of data modifications that is performed as a single logical unit of work. This logical unit of work is typically specified by an explicit set of commands, such as the BEGIN TRANSACTION and END TRANSACTION commands from Visual FoxPro. Throughthe operation of a transaction, the database is essentially “transformed” from one state to another, ideally uninterrupted by any other activity that might be running concurrently.
The classic example of a transaction is the bank customer who uses an ATM to transfer some money from a checking account to a savings account. This requires a logical unit of work that consists of two distinct steps: Deduct the amount from the checking account, and then credit the savings account by the same amount.
If the logical unit of work is broken, either the bank is unhappy (i.e., the money was
applied to the savings account but not removed from checking) or the customer is unhappy (i.e., the money was removed but never deposited). This could happen for a variety of reasons, many of which have been quantified in a set of properties known by the acronym ACID.
ACID properties
The ACID acronym was coined sometime in the early 1980s. It first appeared in a paper presented to the ACM. Since then, databases have the ACID test applied to them in order to discover whether they have “real” transactions. The ACID properties are Atomicity, Consistency, Isolation and Durability.
• Atomicity: A transaction must support a unit of work, which is either committed in whole or discarded in whole. For example, in the ATM example, either both accounts must be updated or neither account can be updated.
• Consistency: This property means that a transaction must leave the data in a consistent state at the completion of the transaction—that is, the transaction can only commit legal results to the database. Updates must conform to any rules programmed into
the system .
• Isolation: Every transaction must be isolated from all other transactions. That is, one transaction cannot read or use data from uncommitted data in another transaction. Any
SQL-92 compliant database (like SQL Server) supports a choice of four different levels of isolation. These levels are discussed later in this chapter.
• Durability: This property requires that a complete transaction be stored permanently, regardless of any type of system failure. That is, a transaction must maintain the unit of work either by permanently storing the changes that it says are committed to the database, or by rolling back the incomplete transaction, even if power fails to the computer at any time
SQL Server fully supports all four properties, but Visual FoxPro supports only the first three—it falls short in the Durability test. The following sections document how both Visual FoxPro and SQL Server fare against the ACID test.
Visual FoxPro transactions
In Visual FoxPro, the BEGIN TRANSACTION, ROLLBACK and END TRANSACTION commands specify the logical unit of work for a transaction, which meets the criteria for the Atomicity transaction property. For example, the following Visual FoxPro code uses a transaction toensure that both the checking account withdrawal and the savings account deposit from the earlier transfer perform as a single unit of work:
*--Code to open checking and savings tables
*--with table buffering happens somewhere up here…
*--Now find the right account records
=SEEK(lnAcctCheck,"Checking","acctid")
=SEEK(lnAcctSave,"Savings","acctid")
*--and update their balances
REPLACE balance WITH balance – 100 IN checking
REPLACE balance WITH balance + 100 IN savings
*--start a transaction
BEGIN TRANSACTION
IF NOT TABLEUPDATE(.T.,.F.,"checking")
*--whoops, bad things happened
ROLLBACK
lcErrMsg = "Unable to update checking account"
ELSE
IF NOT TABLEUPDATE(.T.,.F.,"savings")
*--something went wrong
ROLLBACK
lcErrMsg = "Unable to update savings account"
ELSE
*--all is well, so commit
END TRANSACTION
ENDIF
ENDIF
‡
Remember that Visual FoxPro transactions can only be applied against tables that are associated with a DBC. If the tables are not in a DBC, the transaction has no effect, and changes are applied to the tables regardless
of the ROLLBACK or END TRANSACTION statements.
Through this code, you can also see how the Visual FoxPro transaction qualifies as Consistent, as the TABLEUPDATE() functions fail if any data integrity rules fail, such as field validation rules or referential integrity.
What may not be so obvious is how Visual FoxPro handles the Isolation property of the transaction. Like other database systems, locks are used to maintain the isolation between Visual FoxPro transactions in the form of record locks, header locks and perhaps even filelocks.
During each of the TABLEUPDATE() calls in the previous code, Visual FoxPro will implicitly attempt to lock the records that were modified by the REPLACE commands. In this case, only two record locks are required to ensure the complete isolation of this transaction, whichwill ensure that someone else doesn’t write to either record while this transaction is in progress. In addition, if another process tries to read the data before this transaction executes its END TRANSACTION statement, the other process will read the original, unchanged data.
Inside of a transaction, Visual FoxPro will hold these row locks until either the ROLLBACK or END TRANSACTION statements are issued. This means that the transaction overrides the native TABLEUPDATE() behavior where the locks would normally be released as soon as the modifications were written to disk.
‡
As you may already know, either a ROLLBACK or END TRANSACTION statement completes the logical unit of work, even though the syntax causes one to believe that only the END TRANSACTION statement
completes a Visual FoxPro transaction.
Visual FoxPro’s transaction isolation has performance consequences that are not obvious with this example. Imagine instead that the modifications made during the transaction included not only some record updates, but also the addition of new records. In these kinds ofupdates, it is likely that either a header lock or file lock will be required, which is held throughout the transaction. Since there is only one header or file lock, this can quickly cause lock contention between active transactions, as each must battle to acquire the single header or filelock.
You should also understand that Visual FoxPro implicitly acquires the locks necessary to update an index or memo file, which are stored in a table’s associated CDX and FPT files, respectively. Therefore, if your transaction performs a modification on a field that is part of an index key, or inserts a new record, that table’s CDX file must be locked. There are no record locks in a CDX file—Visual FoxPro locks the entire CDX file. This is also true for FPT files, which means that only one person can lock either of these files at any given time.
This is one of the main reasons that you must keep your transactions as short as possible. If one transaction performs an update that requires an index or memo file lock, all other transactions will be unable to perform similar operations until the locks are released.
The missing property
Visual FoxPro does not support the Durability property of transactions because it cannot ensure the persistence of the modifications made during a transaction. In the preceding example, if the computer hardware, the operating system or the Visual FoxPro application fails before the
END TRANSACTION statement is executed, the data in either table is left untouched, keeping the logical unit of work intact.
However, if such a failure occurs during the execution of the END TRANSACTION statement, there is no mechanism to recover from this failure. Granted, this should be a small window of opportunity, but it does exist, and it grows as more changes are made during the transaction. If this type of failure occurs, you may end up (at best) with a partially committed transaction where the checking account is debited but the savings account is not credited. In the worst-case scenario, the two tables in the transaction may end up corrupted beyond repair.
SQL Server transactions
Now that you are familiar with how Visual FoxPro supports the ACID properties, you may be wondering how SQL Server stacks up. In this competition, the hands-down winner is SQL Server, as it fully supports the critical Durability property as well as providing four distinct levels of Isolation.
The logical unit of work is specified in SQL Server by using the BEGIN TRANSACTION, ROLLBACK TRANSACTION and COMMIT TRANSACTION statements. When you specify these commands, you are using an explicit or user-defined transaction, which we covered in Chapter 3, “Introduction to SQL Server 7.0.” In that chapter, you were also introduced to the idea of Autocommit transactions, in which SQL Server automatically wraps each SQL
statement into its own transaction. To illustrate this, imagine that you execute the following
SQL statement to change all phone numbers in the 408 area code to the 987 area code:
USE Pubs
UPDATE Authors SET Phone = '987 '+Right(Phone,8) WHERE Phone like '408 %'
This statement has the potential to affect multiple records, since it’s unlikely that the WHERE clause will match only one record. If any of the records cannot be updated for any reason, it is important that the entire UPDATE statement fail. This is what Autocommit transactions provide—they automatically wrap any SQL statement into its own implied transaction, as if the following code was written:
USE Pubs
BEGIN TRANSACTION
UPDATE Authors SET Phone = '987 '+Right(Phone,8) WHERE Phone like '408 %'
IF @@Error <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Implicit transactions
There is a third type of transaction in SQL Server, which is different from an Autocommit or an explicit transaction, known as an implicit transaction. Visual FoxPro uses implicit transactions behind the scenes when talking to SQL Server.
Implicit transactions are activated with the SET IMPLICIT_TRANSACTIONS ON
T-SQL statement. The SQL Server ODBC driver issues this statement when you activate the Manual Transactions property setting of a connection. The following code performs this activation, which was first demonstrated in Chapter 6, “Extending Remote Views with SQL PassThrough”:
#INCLUDE FoxPro.h
lnResult = SQLSetProp(lhConn, "TRANSACTIONS", DB_TRANSMANUAL)
You could also set this permanently through the Connection Designer by clearing the “Automatic Transactions” option, but this is not recommended. In either case, once activated, any of the following statements will implicitly begin a transaction on SQL Server. Note that if a transaction is already in effect, these commands do not start a new transaction, as nested transactions are not possible while implicit transactions are in effect.
• ALTER TABLE
• FETCH
• REVOKE
• CREATE
• GRANT
• SELECT
• DELETE
• INSERT
• TRUNCATE TABLE
• DROP
• OPEN
• UPDATE
When using implicit transactions, you must explicitly issue either the ROLLBACK TRANSACTION or COMMIT TRANSACTION command to complete the transaction, even though you did not issue a BEGIN TRANSACTION command. Otherwise, the transactiondoes not complete, potentially blocking other users from accessing the data needed by their transactions. From Visual FoxPro, you can complete the transaction by using the SQLRollback() or SQLCommit() SQL pass through functions. The following example demonstrateshow these functions are employed:
*--open and buffer the views (not shown)
*--grab either view's connection handle
*--assuming they are sharing it
lhConn = CURSORGETPROP("ConnectHandle","Checking")
*--assign data to the view parameters
vnAcctCheck = 10001 && checking acct num
vnAcctSaving = 10002 && savings acct num
*--get the records
REQUERY('Checking')
REQUERY('Savings')
*--make modifications
REPLACE balance WITH balance – 100 IN checking
REPLACE balance WITH balance + 100 IN savings
*--turn on manual transactions
lnResult = SQLSetProp(lhConn,"TRANSACTIONS",DB_TRANSMANUAL)
*--perform updates and finish transaction
IF NOT TABLEUPDATE(.F.,.F.,"Checking")
SQLRollback(lhConn)
lcError = "Unable to update Checking account"
ELSE
IF NOT TABLEUPDATE(.F.,.F.,"Savings")
SQLRollback(lhConn)
lcError = "Unable to update Savings account"
ELSE
SQLCommit(lhConn)
ENDIF
ENDIF
*--restore automatic transactions property
lnResult = SQLSetProp(lhConn,"TRANSACTIONS",DB_TRANSAUTO)
In this example, the CURSORGETPROP() function is used to acquire the connection handle of a view, allowing the SQL pass through statements to share the same connection as the views.
When the SQLSetProp() function is issued to start the manual transaction, the ODBC driver submits the SET IMPLICIT_TRANSACTIONS ON statement, forcing the UPDATE statement (implicitly issued during the TABLEUPDATE() function call) to start a transaction. Ifeither TABLEUPDATE() fails, then the SQLRollback() function is invoked to discard
and complete the transaction; otherwise, the SQLCommit() function commits and completes the transaction.
It is important that you issue the final SQLSetProp() function call to restore the automatic transactions setting. If you skip this step, any of the statements listed previously will start a new implicit transaction, which will prevent other sessions from updating data, since this typeof transaction must be explicitly completed.
Now that you have seen the three different transaction types, you know how to maintain an atomic unit of work for any SQL Server transaction.
SQL Server isolation levels
Another major difference in the way SQL Server handles transactions is in the way transactions are isolated from one another. Visual FoxPro supports only one isolation level, which is more
or less handled internally by Visual FoxPro. SQL Server gives you the choice of four different isolation levels, and you can select a different isolation level at any time, even during a session.
The isolation levels that SQL Server supports derive from the SQL-92 standard. There are four different levels: read uncommitted, read committed, repeatable read, and serializable. To fully understand how these levels are implemented, you may wish to review the section on locking in Chapter 3, “Introduction to SQL Server 7.0,” before continuing.
In considering the different isolation levels, you should know how each level allows you to control the typical anomalies associated with concurrent transactions: uncommitted dependencies, inconsistent analysis and phantom reads.
• An uncommitted dependency is a fancy term for the “dirty read” problem. This problem occurs when one session is allowed to read the uncommitted data from another transaction before it has completed.
• Inconsistent analysis describes a problem also known as a non-repeatable read. This condition occurs when a transaction reads the same data twice, but when it reads the data for the second time, the data has changed. This means that during the first transaction, a secondconcurrently executing transaction has changed the data that was previously read by the first transaction.
• Phantom reads also occur when a transaction reads the same data more than once. For example, imagine a query that retrieves all of the customers within a certain ZIP code. When it is initially executed, only 100 customers are returned. However, when it is executed thesecond time within the same transaction, 102 customers match the criteria. These two extra customers are known as phantoms.
Note that all of these anomalies can be avoided if locks are employed at the right resource (i.e., row, page, table or index) and of the right type (i.e., shared, exclusive and so forth). Having described these anomalies, it is now easier to define how each isolation level prevents one or more of these problems.
• Read uncommitted is the lowest level of isolation, and it provides the greatest amount of throughput, as it virtually eliminates locking between sessions. By enabling this level, you allow transactions to read dirty or uncommitted data. This occurs because the session thatis set to read uncommitted will “read through” any exclusive locks held by other sessions. Furthermore, shared locks are not used when reading data at this level. All of the previously specified anomalies can occur at this level.
• Read committed is the default isolation level of SQL Server transactions. It ensures that the session respects any exclusive locks held by other sessions, preventing the dirty read problem described earlier. However, this isolation level releases any shared locks immediatelyafter the data is read. Therefore, non-repeatable reads and phantom reads can occur at this level.
• Repeatable read is the next highest isolation level available. SQL Server enforces repeatable reads by allowing a transaction to hold the shared locks used to read data until the transaction completes. This prevents other sessions from modifying this data, as datamodifications require an exclusive lock, which is incompatible with shared locks. This isolation level still allows phantom reads to occur.
• Serializable is the highest isolation level available, and therefore it also has the potential for the highest amount of lock contention (read: slowest performance). In serializable transactions, dirty reads, non-repeatable reads and phantoms are eliminated. However, instead ofusing the expected table locks, SQL Server uses something known as a key-range lock. For example, in a query that asks for all customers in a certain ZIP code, the pages of the index that contain the keys that point to the matching records are locked. This prevents other sessions from inserting customer records into the table that would become part of the selected ZIP code.
It is important to understand this level of detail, as it is the only way that you can control how locking is handled by SQL Server. There are no equivalents to Visual FoxPro’s RLOCK() or FLOCK() functions in T-SQL.
Setting isolation levels
At this point, you may be wondering how these levels are implemented. One way to set them is through the SET TRANSACTION ISOLATION LEVEL command, and the other is through the table hints section of any standard SQL statement.
At the session level, the default is to use the read committed isolation level. However, if you desire a particular connection to work at the serializable level, you can issue the following command from Visual FoxPro:
lnResult = SQLExec(lhConn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
Any statements issued across this connection (the one specified by the connection handle lhConn) will now work under the serializable isolation level. This isolation level remains in effect until the connection is dropped, another SET TRANSACTION ISOLATION LEVEL statement is issued, or if any specific SQL statement uses any locking hints to override the locking behavior.
Note that this setting is only for the current connection—other connections are unchanged by this setting, and will operate at their own isolation levels. This means that you can have separate connections that work at different isolation levels, but this is typically not a goodidea, particularly from a debugging standpoint.
The alternative method of setting the isolation level is to use the table hints portion of the FROM clause of a SELECT, UPDATE or DELETE T-SQL statement. With the right table hint, you can override the default connection-level setting of the isolation level for an individualstatement. For example, the following query will override the default connection setting and resort to a level of uncommitted read for the duration of the query:
SELECT COUNT(*) FROM authors WITH (NOLOCK)
Another possible hint is the READPAST hint. When using this locking hint, SQL Server will skip any locked rows. This statement could prevent you from seeing a complete result set, but the benefit is that your transactions will not wait until locks are released before readingthe data. This is a useful tool for determining whether blocking is a problem in specific queries.
If you have a specific query that should operate as serializable, but the session is set at the read committed level (the default), then use the SERIALIZABLE or HOLDLOCK hints (they are interchangeable hints). This will force only the current statement to hold locks;
when used inside of a transaction, this could help prevent unnecessary lock contention, as only the specific statement will use serializable isolation, instead of every table that participates in the transaction.
Durable transactions
The last ACID property to test against SQL Server is Durability, which specifies that a complete transaction must be stored permanently, regardless of any type of system failure. As mentioned earlier, this is the only ACID property where Visual FoxPro is lacking. However, SQL Server transactions qualify as durable. SQL Server implements durability via its transaction log.
As was briefly discussed in Chapter 3, SQL Server uses a technique known as write-ahead logging to ensure the durability of its transactions. This means that any data modifications are
first written synchronously to the transaction log (i.e., your application will wait until it completes this operation before control returns) before they are committed to disk. In fact, there may be a long period of time between the moment when SQL Server receives yourmodification and the moment SQL Server commits the data to disk.
When you submit a data modification statement to SQL Server, the statement is logged. Actually, either the statement itself is logged, or the before and after versions of the data are logged. In either case, enough information is placed into the log so that SQL Server can reconstruct the data modification at any time. Logging occurs for any SQL statement that modifies data (e.g., UPDATE, DELETE or INSERT) as well as statements that create indexes and modify structures (yes, you can roll back a CREATE INDEX statement in SQL Server!).
Once this logging is complete, SQL Server loads the proper extent(s) into a part of memory known as the buffer cache, but only if the data does not already reside there. This is where the data modification is made, allowing any other processes to see the change. In other words, SQL Server, much like Visual FoxPro, tries to read data from memory or write modifications to memory, not directly from disk. Therefore, what resides on disk may not actually be what’s currently held in memory. This is usually referred to as a “dirty” buffer, and it must eventuallyget committed to disk.
On an as-needed basis, SQL Server will write these “dirty” buffers to the actual database files with something known as the Lazy Writer process. When this occurs, SQL Server marks the transaction log so that it understands that logged changes have been committed to disk.This mark is known as a checkpoint, and any logged transactions that occur before the checkpoint
are known to be on disk, while those appearing after the checkpoint are not.
The checkpoint process occurs only after a certain amount of activity has occurred in the database. If it is a slow day in the database, checkpoints will happen less frequently, while on a particularly busy day, checkpoints may occur much more often. This is controlled by something known as the recovery interval, a system-level setting that controls just how much dirty data is allowed to exist at any given time. In advanced situations, you can manipulate this setting to limit the occurrence of checkpoints, but for nearly all situations, the default setting is
appropriate (read: don’t mess with this setting!).
The durability of a SQL Server database becomes apparent when the server fails for some reason (hardware failure, operating system crash and so on). Since SQL Server has logged all data modifications, it can use the log to determine how to recover the database after afailure. For example, imagine that several sessions were updating tables when the power failed on a server. If the workstations received notification that their updates were successful, then their updates have been written to the transaction log successfully. If a workstation did not complete an update successfully, then the transaction log may not contain the COMMIT TRANSACTION statement for that workstation’s transaction.
When the server is restarted, SQL Server starts the recovery process. This process reads the transaction log, starting with the last checkpoint in the log, as this marks the last complete transaction that is known to be written to disk. Any complete transactions that appear afterthe checkpoint are now committed through a roll forward process, while incomplete transactions are automatically rolled back, as some of their changes may have been written to disk.
Note that SQL Server may write the pending changes to disk at any time to make room in the buffer cache for extents that contain pages for other operations. This is why the recovery
operation must roll back incomplete transactions, as some or all of the data could have been committed to disk, even though a checkpoint has not occurred.
Hopefully you now understand why SQL Server’s transactions are superior to those of
Visual FoxPro, as SQL Server passes the ACID test with excellence.
‡
Do not use a write-caching controller with a SQL Server. This will completely destroy the ability of SQL Server to know when changes have actually been written to disk, which eliminates the durability of
the data that the write-ahead logging provides. The exception to this rule is a battery-backed disk controller.
Locking
An important consideration in using transactions is how users are affected by the locking that occurs during transactions. First, let’s review the basic locking strategy employed by SQL Server.
There are several resources that can be locked, such as a row, an index key (i.e., a row in
an index), a page (either a data or index page), an extent, a table, or the database. In normal use of a database, data modifications will only require locks at the row, page or table level. An extent is only locked briefly when SQL Server allocates or drops an extent, while the database lock is used by SQL Server to determine whether someone is “in” a database.
SQL Server usually acquires record locks in favor of page or table locks in most situations. The actual lock that is acquired is based upon the determination of the query optimizer, which uses a cost-based algorithm to determine how locks are placed. Therefore, it is possible that SQL Server may use a series of page locks instead of row locks, as page locks will consume fewer resources than a large number of row locks.
Regardless, for each of these resources, SQL Server can use one of several different lock modes. These include shared, exclusive and update locks, as well as a set of intent locks. Shared locks (S) are used when reading data, while exclusive locks (X) are used in datamodifications. Update locks (U), as discussed in Chapter 3, are used when SQL Server must read the data before modifying it. Update locks are put in place while it reads the data and then “promotes” these locks to exclusive locks when finished reading but prior to making
the modifications.
Intent locks are used to prevent possible performance problems with locks at the row or page levels. When a process intends to lock a row or a page, the higher-level resource in the object hierarchy is first locked with an intent lock. For example, if an S lock is required on a specific row of a table, SQL Server first attempts to get an intent shared (IS) lock on the table and, upon success, attempts an IS lock on the page for that row. If both succeed, then the shared row lock is acquired. By employing intent locks, SQL Server avoids having to scan every row in a page or every page in a table before determining whether the page or table can be locked.
These intent locks include intent shared (IS), intent exclusive (IX) and shared with intent exclusive (SIX) locks. These acronyms (IS, IX, SIX and so forth) are visible when viewing lock activity on the server.
Lock compatibility
In Visual FoxPro, locks are simple, as there is only one lock mode for all of the available resources (record, header or file). That one lock mode, which compares somewhere between a shared lock and an exclusive lock in SQL Server, can be acquired on any of these resources. If oneuser holds a lock, then other users are unable to modify that locked resource until they acquire that lock, but they can read the locked resource.
In SQL Server, the complexity of the various lock modes raises the question of which locks can be acquired concurrently with other locks. The answer to this question can be found in the SQL Server Books Online, in a Help page titled “Lock Compatibility.” This page contains a compatibility matrix, which is reproduced in Table 1.
As you can see from Table 1, shared locks are compatible with other shared locks,
allowing more than one person to read data concurrently. However, exclusive locks are not compatible with any other lock, preventing any kind of concurrent update or reading of data while it is being modified.
Table 1. SQL Server lock compatibility matrix (from Books Online).
Existing granted mode
Requested mode
IS
S
U
IX
SIX
X
Intent shared (IS)
Yes
Yes
Yes
Yes
Yes
No
Shared (S)
Yes
Yes
Yes
No
No
No
Update (U)
Yes
Yes
No
No
No
No
Intent exclusive (IX)
Yes
No
No
Yes
No
No
Shared with intent exclusive (SIX)
Yes
No
No
No
No
No
Exclusive (X)
No
No
No
No
No
No
Blocking
The result of any lock incompatibility is called blocking in the SQL Server documentation. To relate this to your knowledge of Visual FoxPro, blocking occurs when one user is holding a record lock that another user requires. Since the second user cannot acquire a lock until the first is released, the first user blocks the second user. By default, Visual FoxPro will force the blocked user to wait indefinitely for the blocking user to release the lock. However, also by default, the blocked user can press Esc when he or she grows tired of waiting. This behavior
can be changed in Visual FoxPro through the SET REPROCESS command; however, this command has no effect on how SQL Server locks data.
In SQL Server, when blocking occurs, the session being blocked will wait indefinitely for the blocking session to release the conflicting lock. If you wish to modify this behavior, you can use the SET LOCK_TIMEOUT command, which changes the amount of time the session will wait before automatically timing out a lock attempt. When a lock timeout occurs, your TABLEUPDATE or SQLExec call will fail, so use AERROR() to determine the cause of the error. In the returned array, the fifth element will contain error number 1222, which corresponds to aSQL Server Timeout error.
‡
If you change the default lock timeout period, you will need to add code that tests for error 1222. If this error occurs, you must roll back and restart any transactions that are in progress, as a lock timeout error does not
cause SQL Server to abort any transactions.
The lock timeout setting is specified in milliseconds; therefore, to set the timeout to three seconds, issue the following Visual FoxPro command:
lnResult = SQLExec(lhConn,"SET LOCK_TIMEOUT 3000")
You can use @@LOCK_TIMEOUT to query the session’s current lock timeout setting. The default setting is –1, which conveniently corresponds to SET REPROCESS TO –1 in Visual FoxPro:
lnResult = SQLExec(lhConn,"SELECT @@LOCK_TIMEOUT","cTimeout")
If you would like to test blocking, you can do this easily from Visual FoxPro with two views, each of which uses a separate connection. If you share connections between them, you will not be able to test the effects of blocking unless you load two separate instances of Visual FoxPro. To test blocking, start a transaction, and then issue a statement that changes data on the back end. Do not finish the transaction, so that any locks will stay in effect while you use the second connection to attempt access to the same data that is locked in the firstconnection.
The following code snippet demonstrates how this is accomplished:
*--code to open view with buffering
*--grab connect handle from view
lhConn = CURSORGETPROP("ConnectHandle","view1")
*--now start a transaction
SQLSetProp(lhConn,"Transactions","DB_TRANSMANUAL")
*--then issue some kind of change
REPLACE field1 WITH newvalue IN view1
llResult = TABLEUPDATE(.F.,.F.,"view1")
*--only issue next statement after testing
SQLRollback(lhConn)
Obviously, it would be quite beneficial to determine the reason for blocking at any given time. This is accomplished by viewing the current lock activity, either through SQL Server Enterprise Manager or by executing one of SQL Server’s stored procedures from within Visual FoxPro.
Viewing lock activity
The simplest way to view the current lock activity in SQL Server is to fire up the Enterprise Manager (SQL EM). Expand your server in the tree, and then open the management folder to view the management sub-tree. Once you’ve drilled down to this level, you can see the Current Activity node—expand the node by clicking the plus sign. When you expand the node, SQL EM will take a snapshot of the activity on the server at that moment and fill the sub-nodes with this information.
To view general information about all processes and the possibility of blocking, click on the Process Info node. This will display the screen pictured in Figure 1, which shows one row for each connection to the server. The first six SPIDs are system processes that have nothingto do with Visual FoxPro connections to the server. However, SPIDs 9 and 10 are two Visual FoxPro connections, both of which have open transactions.
Figure 1. Viewing process information in the Enterprise Manager.
From this view, you can see that SPID 10 is waiting for a locked resource from another process. To see which process is doing the blocking, you would have to scroll and view the last two columns, titled “Blocked By” and “Blocking,” respectively. Figure 2 shows these last two columns, and shows clearly that SPID 9 is blocking SPID 10. Note that you have to scroll back to the left to see which rows correspond to which SPID.
Figure 2. Viewing blocking information.
Of course, this dialog only gives you a single clue as to what is causing the blocking between these two processes. The Wait Resource column shows that a KEY lock is preventing SPID 10 from progressing. To get more detail, you can expand the Locks/Process node, and then select the desired SPID from the list. This will display a list of the locks that the process has acquired, as well as any locks that it is waiting to acquire. Take a peek at Figure 3 for the output that is seen for the current situation where SPID 9 blocks SPID 10.
In this figure, you can clearly see that the process is waiting for a KEY lock in the authors
table, while it has already acquired an intent shared (IS) lock on the page and the table.
Figure 3. Viewing process locking details.
While you are in the process list, you can double-click any of the SPID’s icons to get a dialog that displays the Process Details, which includes a very handy display of the last T-SQL command batch issued by the process. Since SPID 10 is blocked by another process, this allows you to see what commands the blocked process issued, which could help you determine the cause of the blockage.
Of course, all of this information is great, but what if you do not have access to SQL EM? Fortunately, you can access most of this information from Visual FoxPro, but it must be from an unblocked process! The system stored procedures sp_who, sp_who2 and sp_lock return cursors of data about current activity on the SQL Server. These stored procedures can be executed with Visual FoxPro’s SQLExec SQL pass through function.
Note that sp_who2 is an undocumented procedure that returns additional information over that of sp_who. Both of these procedures return information about each SPID, including the information viewed in the current activity window (in fact, it seems sp_who2 is called by SQL EM for the current activity window). The sp_lock procedure returns locking information
about all processes, and returns the same information as the Locks windows under the current activity tree.
All of these procedures accept a parameter for the number of the desired SPID. For example, the following Visual FoxPro code calls the sp_who2 procedure to retrieve
information about the SPID for the current connection, accessed by using the @@spid system function, and places the result into a cursor called cWho2:
lnResult = SQLExec(lhConn,"EXECUTE sp_who2 @@spid","cWho2")
You may invoke these procedures without specifying any parameter in order to retrieve information about all processes.
Deadlocks
Deadlocks are a different concept than blocking and should be treated as a completely different problem. While blocking is usually only temporary, a deadlock will last indefinitely if not explicitly handled. To understand a deadlock, imagine the following scenario: Two users are accessing the same database. Within a transaction, the first user accesses the customer table
and attempts to change information in the record for Micro Endeavors, Inc. in that table. Meanwhile, also within a transaction, the second user accesses the contact table and attempts to change the phone number for Mr. Hentzen. For some reason, the first user now attempts, within the same transaction, to change the same record in the contact table, and the second user attempts to change the same record in the customer table.
Since the first user holds an exclusive lock on the customer record, the second user is waiting for that lock to be released in order to continue. However, the second user is holding a lock on the contact record, forcing the first user to wait for that lock to be released before continuing. There you have it: a deadlock, also known as a circular chain of lock requests.
If this situation were to happen in Visual FoxPro, with any luck you will have changed SET REPROCESS from its default so that at least one of the two processes would automatically fail in its attempt to get the second lock. When the user’s lock attempt fails, they would begiven the chance to try their transaction again, and most likely would succeed.
In SQL Server, this situation is automatically handled by an internal scheme for detecting deadlocks. When SQL Server detects a deadlock, one process is chosen as the deadlock victim and that transaction is automatically rolled back. SQL Server chooses the process that hasthe least amount of activity as the victim, and when the transaction is canceled, an error is returned to the application. This means that your application must always detect error number 1205 when issuing a TABLEUPDATE or SQLExec call. This error can occur before a transaction has completed. When error 1205 is detected, you must restart your transaction, since the server has already rolled back the transaction for the deadlock victim.
Deadlocks can be avoided by ensuring that all of your stored procedures and Visual FoxPro code access resources in the same order at all times. In other words, if both of the aforementioned users attempted to access the customer table first, and then the contact table, thedeadlock would not have occurred. However, since this requirement cannot always be met in the real world, you will need to add code to detect when the user is the victim of a deadlock and handle it accordingly.
Occasionally, it is necessary to set one process at a lower priority level than another for the purpose of resolving deadlocks. If this is the case, you can use the SET DEADLOCK_PRIORITY command to establish the sessions that should be the preferred victims of deadlocks.
Transaction gotcha!
After reading all of this information about how SQL Server and Visual FoxPro handle transactions, you still may not be aware of the fact that Visual FoxPro transactions do nothing to start, end or commit transactions on the back-end database. Consider the following Visual FoxProcode that attempts to update two remote views:
*--views opened with buffering
BEGIN TRANSACTION
IF NOT TABLEUPDATE(.T.,.F,"view1")
ROLLBACK
ELSE
IF NOT TABLEUPDATE(.T.,.F.,"view2")
ROLLBACK
ELSE
END TRANSACTION
ENDIF
ENDIF
In this example, a Visual FoxPro transaction is started and wraps the updates of two different views. Unfortunately, in client/server applications, these updates are applied to remote tables, not Visual FoxPro tables. Therefore, the TABLEUPDATE() statements are not affectedby the Visual FoxPro transaction, thereby writing their changes immediately to the source tables.
In other words, if the first TABLEUPDATE() succeeds but the second one fails, the ROLLBACK command has no effect whatsoever. The solution? Look earlier in this chapter for the code that starts a transaction by setting the Transactions property of the connection tomanual and submits the SQLRollback() or SQLCommit() SQL pass through functions.
No matter what, do not use Visual FoxPro transactions against remote views.
Summary
In this chapter, you have seen a comparison of Visual FoxPro and SQL Server transactions. The ACID properties are used to test the quality of transactions by a database system. Visual FoxPro falls a bit short, but SQL Server transactions are fully compliant with the ACID standard. You also learned how blocking and deadlocks occur and how to retrieve the information that SQL Server provides on locks that each process is holding.
In the next chapter, you will switch gears entirely and see how to use the basics of ADO in a Visual FoxPro application.
Posts
No comments:
Post a Comment