Web Hosting Java Blog

Chapter 6 PROGRAMMING SQL SERVER 2000 365 Atomicity. A transaction cannot be broken up into smaller units; either the entire transaction happens or none of it happens. Consistency. A completed transaction leaves the database in a consistent state. Isolation. A transaction is isolated from other transactions in the database, so transactions can t overwrite each other s data. Transactions, in other words, can t interfere with other transactions that are running concurrently. Durability. A transaction, after it has been applied, sticks in the database. These qualities are easy to recall by remembering the word ACID : Atomicity, Consistency, Isolation, and Durability. SQL Server provides the atomicity, isolation, and durability for you, but it s up to you to make sure that a transaction leaves the database in a consistent state. Atomicity and durability are handled by the transaction logging system and, to an extent, by the lazy writer, which was covered back in Chapter 2, Data Modeling. Isolation is handled by the lock manager, which is covered in this chapter. Designing and Managing Transactions . Design and manage transactions. If you don t explicitly tell SQL Server to treat a group of statements as a transaction, it implicitly puts each statement in its own transaction. For the purposes of an implicit transaction, the only statements that really count are the statements that interact with a database: SELECT, INSERT, UPDATE, and DELETE. To explicitly put a group of statements into a transaction, you can use the BEGIN TRANSACTION command. This command tells SQL Server all commands that follow up until the end of the transaction, which is noted with an COMMIT TRANSACTION. In the event of a problem with the data being manipulated, you can also call ROLLBACK TRANSACTION. If there is an error during the execution of the transaction, such as a server shutdown, a disk error of some type, or lock contention, then the transaction automatically rolls back.

For high quality java hosting services please check java web hosting website.

364 Part I EXAM PREPARATION There is one more aspect of locking to discuss. SQL Server also has the capability to lock objects at different levels to increase performance. This is called lock granularity. Lock Granularity In addition to shared and exclusive locks, SQL Server also locks objects at different levels. SQL Server can lock a single row of a table, a single data page, or an entire table. Typically, SQL Server operates in the page lock mode, where it locks the data pages being requested. After a certain amount of blocking is noticed, SQL Server slips into a row locking mode, where single rows are locked. On the other end of the scale, when a connection attempts to update a certain percentage of a table, SQL Server automatically escalates to a table lock, where it automatically locks the entire table either exclusively (in the case of a full table update), or shared (in the case of a full table read). SQL Server also determines lock escalation based on the activity occurring in the table at the time of the lock request. If the activity level is low, it saves itself some time by just escalating the lock sooner because it will have less effect on other users. That means there are shared page locks, shared row locks, and shared table locks for reads, along with exclusive page locks, exclusive row locks, and exclusive table locks for writes. ENFORCING PROCEDURAL BUSINESS LOGIC USING TRANSACTIONS . Enforce procedural business logic by using transactions. Design and manage transactions. Manage control of flow. Each statement that accesses a database is enclosed in a transaction. A transaction implies several things:

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 363 To avoid deadlocks, make sure that all the objects are always accessed in the same order. Make sure that in cases where a series of updates to different tables are done, they are always done in the same order. Keep transactions as short as possible, prevent user interaction within transactions, and set a low isolation level. What s an isolation level? Good question. Isolation Levels SQL Server knows that sometimes it s critical that the data you are reading from the database is absolutely one hundred percent committed data, whereas at other times you want the data to be read quickly, and incomplete or uncommitted transactions just don t matter. To accommodate this, SQL Server supports four different transaction isolation levels: Read Uncommitted. This isolation level shows you all the data without getting a shared lock first. Another connection may change the data while it is being read. This can be great for applications that are doing system monitoring or reporting, where minimal impact to the rest of the system is desired. This is also called dirty reads. Read Committed. This isolation level acquires a shared lock during the read of the data, but doesn t keep the shared lock for the entire transaction. The resulting data is complete, but may change after successive reads, showing new data or indicating missing data with each successive read. This is the default transaction isolation level, and is generally an acceptable tradeoff between reading dirty data and minimizing contention. Repeatable Read. This isolation level acquires a shared lock on the rows for the duration of the transaction, but still allows other users to add rows into the result set. That means that later reads may contain more data, but they won t contain any less. Serializable. This isolation level acquires a shared lock on the entire range of data that is being queried, preventing inserts or updates from happening for the duration of the transaction. This is a very dangerous thing to do from a concurrency perspective, because it generates a lot of locks and can more easily result in deadlock problems.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

362 Part I EXAM PREPARATION In Figure 6.1, you can see that there are two users: User 1 and User 2. User 1 wants to do two things: read a record from the Parts table and write a record to the Sales table. User 2 wants to do two similar things, but he s going to read from the Sales table and write to the Parts table. Now, step through what happens if both transactions start at the same time: 1. Both users execute a BEGIN TRANSACTION statement. 2. User 1 executes a SELECT, which results in a lock on one or possibly several pages in the Parts table. User 2 executes a SELECT, which results in a lock on one or possibly several pages in the Sales table. 3. To continue, User 1 needs a lock on some of the pages in the Sales table. Unfortunately, User 2 has the pages locked already, so User 1 goes into a state of waiting for the lock to be resolved. At the same time, User 2 needs a lock on some pages in the Parts table, which User 1 already has a lock on, so he goes into a waiting state for the lock to be resolved. 4. The SQL Server lock manager process is watching the locks, and notices that neither of these processes can proceed, because they both have something the other needs exclusive control of to proceed. SQL Server then (according to the rules in the note) terminates one of the transactions. The other process runs to completion; the process that was killed receives an error (Error 1205) and terminates. User 1 User 2 Start Transaction Read Data from Parts Table with holdlock Write Data to Sales Table End Transaction Start Transaction Read Data from Sales Table with holdlock Write Data to Parts Table End Transaction FIGURE 6.1 This is an example of a set of transactions that could cause a deadlock.

For high quality website hosting services please check cheap web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 361 Make transactions as simple as possible, but no simpler. Keep extraneous logic out of the transaction. The best case is when you do all the gathering of data and validation of that data outside of the transaction, and the transaction is used only to update and insert rows. Make sure that the application does not have any transactions that wait for user input. A transaction should be able to complete from beginning to end without waiting for a user to enter any data. What s the big deal? Users tend to do things like go to lunch, usually while they have windows open, waiting for them to enter data to complete their transactions. Collect all the data at once, then start the transaction, make the changes, and commit. Design applications and databases with concurrency in mind. Keep tables that are frequently updated small by moving columns that don t belong in the table or that aren t changed as often into another table. If a table is going to be updated frequently, make sure it isn t indexed more than necessary. Data modification statements, such as INSERT, UPDATE, and DELETE have to change the indexes as they go, so having too many indexes on a table requires them to modify several indexes. Deadlocks Who Lives and Who Dies? Which Deadlocks occur when two or more transactions cannot complete process is killed in a deadlock isn t because of mutual locks. For example, if User A needs to update a exactly random. If one user is the row in the Deposit table and then a row in the Withdrawal table, System Administrator and the other whereas User B needs to update the Withdrawal table and then the one is just a normal user, the normal user s process is terminated. Deposit table, there will be an instant in time when User A has an Otherwise, SQL Server picks the user exclusive lock on the Deposit table and to complete his transaction that has the least to lose from having he needs a lock on the Withdrawal table. Contrarily, User B has a its transaction terminated. If they re lock on the record that User A needs in the Withdrawal table and both equal, then SQL Server picks one needs a lock on the record in the Deposit table that User A already at random. It s also possible to set a has locked. In this case, SQL Server detects the deadlock and more connection-level parameter with SET or less randomly kills one of the user processes. DEADLOCK_PRIORITY LOW to tell SQL Server that the transaction can be ter- Deadlocking is very important, so the next few paragraphs repeat a minated if it is involved in a deadlock. lot of what you just read, but from a different angle, just to make sure you ve got it down. NOTE

For high quality java hosting services please check java web hosting website.

360 Part I EXAM PREPARATION CONCURRENCY AND LOCKING One of the hallmarks of a true database management system is whether it has the capability to handle more than one user performing simultaneous data modifications. The problem is that when several users in a database make changes, it s likely that they eventually will all want to update the same record at the same time. To avoid the problems that this would cause, SQL Server and most database management systems provide a locking mechanism. A locking mechanism provides a way to check out a particular row or set of rows from the database, marking them so they cannot be changed by another user until the connection is finished and the changes are made. For connections that are reading data, locking provides a mechanism to prevent other connections from changing the data for the duration of the read or longer. There are two basic types of locks: shared locks and exclusive locks. A shared lock happens when a user is trying to read a row of data; for some duration, depending on the transaction isolation level (which is covered later in this chapter), the user owns a shared lock on the table. Because the user is just trying to read the record, there can be several shared locks on the row, so many people can read the same record at the same time. Users obtain exclusive locks when the user needs to change the row. Exclusive locks are not shared; there can be only one user with an exclusive lock on a row at any given time. Lock Contention If a user needs to acquire an exclusive lock to a row that is already locked by another user, the result is lock contention. Some level of contention is normal in a database that is being frequently updated. Typically, an application waits for some arbitrary amount of time for the locks to clear and the transaction to complete. This results in an apparent slowdown of the application and the server, and excessive amounts of contention lead to performance degradation and possibly user complaints. There are a few things you can do to reduce lock contention.

For high quality website hosting services please check cheap web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 359 Procedure sp_describe_cursor_tables Description Returns the tables used by a cursor, which you ll need to have for an UPDATE WHERE CURRENT OF statement. These stored procedures can help you debug your cursor problems by enabling you to create the cursor and then track down what kind of cursor SQL Server is actually using. For example, if you declare a FORWARD_ONLY cursor, is it dynamic or static? That s more than you ll ever need to know about cursors in real life, and enough to cover what you ll need to know for the exam. REVIEW BREAK Writing Scripts Using Statements, Comments, and More We re about halfway through the chapter, so it s time for a quick review of some of the key points we ve covered. . Programming in SQL Server is done in the T-SQL language by writing scripts that are made up of batches, which are groups of statements. . Variables are local objects that can be used to store temporary values, such as counters. . Comments are used to make T-SQL batches easier to understand and to temporarily disable statements for debugging. . The IF…ELSE construct can be used to conditionally execute statements, and the WHILE construct can be used to repeat statements. . Cursors are used to work on rowsets one row at a time. They are useful, but not as efficient as direct statements on sets. That s quite a bit of hands-on material, from writing scripts to managing loops and cursors. We still have more to cover in this chapter, including theory on locking and important points about transactions.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

358 Part I EXAM PREPARATION IN THE FIELD THE PROPER USE OF CURSORS We tend not to be very nice to the people we are interviewing for a database administrator position. One of the things that happens during the process is having the candidate stand up at the whiteboard and tell the interviewers how he d approach different database problems. One favorite is to ask the candidate to write the syntax for using a cursor on the board. If they get it right it s a big negative mark. We don t want to hire someone who is too good with cursors, because that means that they use them too much. If you approach every single problem with the attitude that it can be solved without a cursor, you will find a way to solve it without a cursor, and it will probably run faster and cause fewer problems than a solution that uses a cursor. Use a cursor like a carpenter uses a sledgehammer. It s out in his truck, it s really heavy, and if there s any other way to solve the problem that doesn t involve walking all the way out to the truck and lugging a 20-pound hammer up into a house, he ll use that instead. Sometimes a sledgehammer is the right tool for the job. But not very often. Table 6.3 lists some interesting stored procedures that can be used to find out what cursors are available to the current connection and describes some of their properties. TABLE 6.3 CURSOR STORED PROCEDURES Procedure Description sp_cursor_list Returns a list of all the declared cursors available to the current connection with some of their properties. sp_describe_cursor Returns the properties of the cursor, such as FORWARD ONLY and READ_ONLY. sp_describe_cursor_columns Returns the columns that are found in a cursor and their data types.

For reliable and cheap web hosting services please check javaweb hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 357 Extension Description OPTIMISTIC This causes SQL Server to not lock any rows during the scrolling of the cursor, and you have to just hope that none of the rows being changed by the cursor is simultaneously being changed by somebody else. Attempting to change a row through the cursor results in an error. TYPE_WARNING If somehow your cursor changes type implicitly, a warning is issued. A few notes on the table. First, the default LOCAL or GLOBAL status of a cursor can be changed by changing the server-wide Default To Local Cursor configuration setting with sp_configure. Next, if you specify FORWARD_ONLY, and don t specify STATIC or KEYSET, the cursor behaves as a DYNAMIC cursor. In other words, the cursor sees any records inserted by other connections while the cursor is open. In addition, if you don t use the SCROLL, STATIC, KEYSET, or DYNAMIC options to specify that a cursor should scroll, the cursor will be FORWARD_ONLY. Also, you cannot use FORWARD_ONLY and FAST_FORWARD together. All that said, it pays to specifically spell everything out in your DECLARE statement to make it very obvious what you are attempting to do with your cursor. In other words, if you re doing a forward- only, updateable cursor, you could just use the normal DECLARE cursor foo for
, but it is better to do something like DECLARE cursor foo forward_only for for UPDATE of . That way, it is easy to tell exactly what that cursor is going to be used for and what restrictions there are on the use of the cursor. So, when is it proper to use cursors? You should never use a cursor when you can write a better UPDATE statement to avoid using a cursor altogether. Cursors consume a lot of SQL Server resources, and they are nowhere near as fast as just running a single UPDATE statement, or even multiple UPDATE statements. Avoid using cursors. If you use cursors only when you absolutely have to, then you ll be using them properly.

For high quality java hosting services please check java web hosting website.

356 Part I EXAM PREPARATION TABLE 6.2 TRANSACT-SQL CURSOR EXTENSIONS Extension Description LOCAL This is the optional state for a cursor. It means the cursor is available for only the current batch and the current connection. To change the default behavior, set the Default to Local Cursor database option. GLOBAL Global in this case means Global to the current connection. Declaring a cursor as global makes it available to subsequent batches or stored procedures that are run by the connection. The cursor is not available to other connections, even if the connection is from the same user. FORWARD_ONLY This tells SQL Server that the cursor is going to run only from the beginning of the recordset to the end of the recordset. The cursor is not allowed to go backward or skip around. The only fetch that works is FETCH NEXT. This is an optimization; it allows SQL Server to consume less overhead for the cursor. STATIC This does the same thing as the INSENSITIVE keyword in the SQL-92 syntax. KEYSET If you use this, your cursor will not be able to access data inserted by other users after the cursor is opened, and if a row is deleted by another user, an @@FETCH_STATUS of -2 (row is missing) will be returned if you attempt to fetch a deleted row. This type of cursor has less overhead than a DYNAMIC cursor, but (unless FORWARD_ONLY is also specified) all the different FETCH options are available. DYNAMIC A DYNAMIC cursor is the opposite of a KEYSET cursor. All inserts and deletes done by users are immediately available to the cursor. However, FETCH ABSOLUTE does not work with a dynamic cursor because the underlying data may change the position of the records. FAST_FORWARD This is a cursor that has all the properties of a FORWARD ONLY and READ_ONLY cursor, and is designed to go forward quickly with little overhead. READ_ONLY Does not allow updates to the cursor. SCROLL_LOCKS This causes SQL Server to exclusively lock each row that is touched by the cursor as they are read in, to prevent other users from updating the record.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.