Chapter 6 PROGRAMMING SQL SERVER 2000 385 A
Chapter 6 PROGRAMMING SQL SERVER 2000 385 A PPLY YOUR K NO WLEDGE batches that are being run at the time that the users are getting errors. Here are the batches: Batch 1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION Declare @ProductID int Select @ProductID = ProdCode From Product .where ProductName like Deluxe Widget Update SalesTracker Set ProdCode = @ProductID where SalesID = 19 Commit Transaction Batch 2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION Declare @SalesID int Select @SalesID from SalesTracker where .ProductCode = 42 Update Product Set SalesCode = @SalesID where ProductCode = 42 Commit Transaction What s a likely cause of the problem? A. Lock contention in the Product table. B. Lock contention in the SalesTracker table. C. A deadlock between the Product table and the SalesTracker table. D. All of the above. Answers to Review Questions 1. A script contains one or more batches. A batch contains zero or more statements. A transaction contains zero or more statements. A batch has no relation to a transaction. 2. Locking is important because it enables multiple users to access the database at the same time without having the users tromp on each other s data. Without locking, users could not manipulate data without partially overwriting each other. 3. Most SELECT statements should be done outside a transaction to keep the transactions shorter. To make transactions run quickly and reduce lock contention, you need to make sure the statements between BEGIN TRANS and COMMIT TRANS are the ones that need to be there. The exception is when you need to pull the most recent value possible from a table, and there is a chance it may change from outside the transaction to inside the transaction. 4. Generally, you shouldn t use a cursor. But you will have to when the operation you want to perform on each row is dependent upon the operations you ve performed on previous rows. For example, if you want to write an algorithm to find a statistical median, you have to use a cursor. Statistical mean, however, is a built-in function, and you can get a statistical MODE with a count(*) and GROUP BY. 5. You can do two common things to reduce deadlocking in a database. First, always access objects in the same order. It doesn t matter what order alphabetical order is fine it just has to be the same order all the time. Second, keep transactions as short as possible to reduce lock contention. Generally, anything that you can do to reduce lock contention also reduces deadlocking. 6. Static cursors are created from a copy of the data set, and do not reflect changes made to the underlying data after the initial instantiation, which occurs at the OPEN statement. Dynamic cursors always reflect changes in the underlying data. 7. A FORWARD_ONLY cursor can only be used with FETCH NEXT; it cannot be used with FETCH FIRST, FETCH LAST, and so on. It also cannot go backward through the rowset.
If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.