19. Understand the transaction and transaction lock
What is a transaction?
ACID attribute
Trading mode
Transaction recovery
Transaction lock
Block and dead knot
Lock prompt
Summary of this chapter
This chapter will clarify the basic law of transactions and transaction locks, learning what is transaction, Microsoft SQL Sever 2000's attributes required by the effective exchange, the start and end of the transaction can specify which trading mode is used, and how to recognize and restore transactions; See the lock type and mode used by SQL Server when performing the transaction, and the concept of "Blocking" and "Deadlocking"; finally introduces some of the information tables you may use in the transaction.
What is a transaction? "Transaction" is a series of related homeworks that are performed in a single logic unit. Transactions make SQL Server to ensure a certain level of data integrity and data response. Each database must have a so-called "Transaction Log" for saving all transaction records for modifying (insert, update, or deleting) for the database. In the event of an error or system failure, SQL Server uses the transaction record file to reply to the information. The integrity of the transaction, partial relying on programming staff is reached. The programming must know when to start trading, when the transaction is terminated, and in any order to make sure to ensure the logic consistency and meaning of the data. Now you know what is transaction, let's take a look at the attributes of the effective trading requirements.
A transaction must meet four specific requirements to qualify as a valid transaction, which is called "ACID Properties", and the ACID is "atomicity", "consistent", "" Isolation (isolation "and" Durability "English abbreviation. SQL Server provides a mechanism to help confirm whether a transaction meets each request. If a part of the completion, if the transaction is successful, then SQL Server guarantees that all information modifications in the transaction are completed, and when the transaction is unsuccessful, it will not be modified. In other words, SQL Server ensures that the transaction is not partially completed. The transaction must be performed as an indivisible basic unit, so it is called an indispensable completion. To be successful, every step (or statement) of the transaction must be successful. If a step fails, the entire transaction has failed, and the modifications made after the start of the transaction will be canceled. SQL Server provides a transaction management mechanism that automatically performs tasks that determine the success or failure of the transaction, and if it fails, it will definitely cancel all information modifications. Consistency SQL Server also ensures consistency of transactions. The consistency means that all information remains consistent after the transaction is completed, which ensures the integrity of the information, regardless of the success of the transaction or fail. Prior to the transaction, the database must be in a consistent state - that is, the data maintains its integrity, and inherent structures, such as the B-Tree index, and dual link lists, it is correct. After the transaction occurs, the database must also maintain a consistent state, if the transaction is successful, or if the transaction fails, it will remain consistent with the state before the transaction begins.
Consistency is also the transaction management characteristics provided by SQL Server. If your information is consistent, the transaction will remain logical and the data is complete, and SQL Server will ensure the consistency of data after the transaction. When you use data in a decentralized environment, you can reach different levels of consistency, and its range is included from the last transaction convergence (or potential consistency) to instant transaction consistency. The consistency level depends on the copy type you use. For more information, see Chapter 26 to Chapter 28. Isolation
"Isolation" means that the role of each transaction will be like this transaction is the only transaction in the system; that is, the modification of an exchange with any other simultaneous transactions is isolated. Another transaction will not be affected by the value of this transaction change before the change of the transaction is recognized. If the transaction fails, its modification does not have any effect because the change will be returned. SQL Server allows you to decide the isolation level of the transaction. The isolation action of the transaction depends on the isolation level you specified.
Description
When the transaction is completed, all of its modifications becomes the permanent part of the database. When the transaction is returned, the change will be canceled, the database looks like the transaction has never happened.
Isolation grade
SQL Server supports four levels of isolation. The isolation level is a setting that determines the level of allowing transactions to accept inconsistencies - ie the degree of transaction and another transaction isolation. A higher isolation level increases the correctness of the information, but it reduces the number of parallel transactions. On the other hand, the lower isolation level will allow more transactions in parallel, but it reduces the accuracy of the data. You determine the locking behavior of all SELECT statements during the work phase (unless you set to another quarantine) during the work phase. The locking behavior will be discussed in the "transaction lock" section later in this chapter.
Reading unidentified: the lowest level of isolation. In this level, transaction isolation is only enough to ensure that the information is destroyed by the entity is not readable. Read the approve: SQL Server's preset level. In this level, only the recognized information is allowed (the recognized information is a data that has become a permanent part of the database). Repeated reading: In this level, a transaction reading of the same data column or number of information columns will result in the same result. (Until the transaction is completed, no other transaction can modify the information.)
Serializable: The highest level of isolation is completely isolated between each other. In this level, the results of multiple transactions are simultaneously performed simultaneously with the transaction sequence (ie one order one after another). Parallel trading behavior is easier to understand each isolation level, first look at the three behaviors that may occur when you perform parallel transactions. DIRTY reads: that is, the unidentified data is read. DIRTY READ occurs in a transaction modification information, while the second transaction reads the modified information before the first transaction recognizes changes. If the first transaction restore changes, the second transaction will retrieve the information not available in the database. Nonweigh read: Rehabilitation read will get inconsistent information. Non-rehabilitation reading occurs during the same transaction multiple times of reading, and another independent transaction updates the information column data. Because the recovery reading of the first transaction will retrieve different information, the result is unusable in this transaction. Phantom Read: A transaction tried to retrieve a non-existing data column, but the other transaction in front of the transaction has inserted the information column. If the first transaction looks for the information again, it will find that the information column has suddenly appeared. This new information is called "phantom row". Table 19-1 lists the type of behavior allowed for each isolation level. As you can see, "Reading Unrecognizes" is limited to the minimum isolation level, and "serialization" is the most stringent isolation level. As mentioned earlier, the preset isolation level of SQL Server is "reading approve". As the isolation level is improved, SQL Server will use a longer period of time to control more stringent restrictions. The isolation level affects the locking behavior of the SELECT statement, which indicates that the isolation affects the lock mode used by the read data. The lock mode will be discussed in the
Split NonoNono
Setting the Isolation Level Transmits the function in the T-SQL statement or application, you can set the isolation level used in the entire SQL Server user's working phase, but also specify the lock prompt in the query to overwrite the specific transaction has been set. grade. The lock reminder will be discussed in the "Lock Tips" section later in this chapter. To use T-SQL or set an isolation level in a DB-LIB application, use the Set Transaction Isolation Level and specify one of the four isolation levels. The syntax is as follows:
Set Transaction Isolation Level
Read uncommitted
R r r c
| RepeATable Read
| Serializable
Go
Related Information For other types of applications, such as using ODBC, ADO, or OLE-DB, please check the "ACID Properties" in the Book index on the line, and select "Adjust Transaction Isolation" in the "Found Topic" dialogue.
Once you set the isolation level, in the next transaction, the SQL Server work phase will perform the lockup that the isolation level is determined. Using the DBCC UserOptions directive, you can find which isolation level of SQL Server is currently preset. The syntax of the instruction is just a directive name:
DBCC UserOptions
This instruction is transmitted back to the user that the user has set or currently in the startup state. If you do not set an isolation level (let it be a SQL Server preset level), you will not see the isolation level when you perform the DBCC UserOptions command; however, if you specify a non-preset level, you will see the isolation level. For example, if you perform the following statement, the isolation level will be displayed in the output of DBCC UserOptions: USE PUBS
Go
Set Transaction Isolation Level Serializable
Go
DBCC UserOptions
Go
DBCC UserOptions
The result of the command of the instruction is like this:
Set Option Value
-------------------------------------------
Textsize 64512
Language us_english
DateFormat MDY
DateFirst 7
Quoted_identifier set
Arithabort Set
ANSI_NULL_DFLT_ON SET
ANSI_DEFAULTS SET
ANSI_WARNINGS SET
ANSI_PADDING SET
ANSI_NULLS SET
Concat_null_yields_null set
Isolation_level Serializable
(13 row (s) affected)
You can use the lock prompt in the data table level to overwrite the preset isolation level, but you can only use this technology in the case where it is required and fully understood how it affects your transaction. To get more details about the available lockouts, please refer to this chapter later
Real world uses a foreign transaction to let's take a look at what we need to start and terminate a task using a foreign transaction. Suppose we have a pre-depicting program named Place_Order, which is used to handle the database task of the user ordering project. The procedure of the program includes selecting the user's current account information, entering a new order ID number and ordered project, calculates the tax-included order price, updating the customer account balance to reflect the total price, and check if the project is in stock. Let's take a look at what we need to start and terminate a task using a foreign transaction. Suppose we have a pre-depicting program named Place_Order, which is used to handle the database task of the user ordering project. The procedure of the program includes selecting the user's current account information, entering a new order ID number and ordered project, calculates the tax-included order price, updating the customer account balance to reflect the total price, and check if the project is in stock. We hope that these steps are completed or not completed to maintain the consistency of information in the database. In order to achieve this, we will handle the statement of these tasks as a glue transaction. If we don't combine these statements, we may get inconsistent information. For example, if the user's balance is updated after the number of customer balances has been executed, the user ends to the server's network connection is interrupted, and the customer has a new order in the database, and the customer's account has no cost expenditure. SQL Server itself will be approved as quickly as possible, and the pre-depiction program is only half when the network is disconnected. However, in a foreign transaction, if the step is defined, the SQL Server will automatically restore the entire transaction when the line is disconnected, and the client can be connected again and execute the program again. To learn more about more details, please refer to this chapter later
Commit [TRAN [SACTION] [TRAN_NAME | @TRAN_NAME_VARIABLE]]]
Rollback [TRAN [SACTION] [TRAN_NAME | @TRAN_NAME_VARIABLE
| savepoint_name | @savepoint_name_variable]]
The recognition transaction has been mentioned above, an accredited transaction is all modifications have become a transaction in the data library. Before the transaction is recognized, a record and recognized record of transaction modification will be written to the database transaction record file. Therefore, it has become one of the two positions of the database will be one of the following two locations: Modification does not have written to the disk into the permanent part of the database, it is located in the data. If the system has an error, the transaction record file can take this to restore the transaction, and the transaction will not have any lost. All resources used by the transaction, such as lockout, will be released as recognized. If each statement is successful, the transaction will succeed and will be approved. Below is a small exotic transaction called Update_State, which updates all the STATE data behavior null values in all issuers' Publishers tables to XX:
USE PUBS
Go
Begin TRAN UPDATE_STATEUPDATE PUBLISHERS SET State = 'XX'
Where State Is Null
Commit tran update_state
Go
If you execute the transaction, you should see that the two data has changed. To restore the data table to the original state (just like recovery instead of approved), do the following transactions:
USE PUBS
Go
Begin TRAN UNDO_UPDATE_STATE
Update Publishers Set State = NULL
Where stat = 'xx'
Commit TRAN UNDO_UPDATE_STATE
Go
You can see that there have been two data lines. With the transaction name UPDATE_STATE and undo_update_state used by the commit TRAN, it will be ignored by SQL Server - it is just helping program designers to determine which transaction will be recognized. SQL Server Automatically recognizes the latest unidentified transactions before committing, whether or not the trading name is specified. Nest-like transaction SQL Server allows "Nested Transactions" or "transactions in transactions". When using a nest, you should approve each inner trade, then SQL Server will know that the transaction has been completed and the resources used in the transaction are released when the outer transaction is recognized. If the resource is locked, other users will not access those resources. Although each transaction must contain commits, it is successfully recognized by the transaction to the outermost layer, and SQL Server truly recognizes the inner deal and releases the resources used in the inner layer and outer transactions. If the outermost transaction recognition fails, the inner dealers will not be recognized, and all internal and external transactions will be restored. If the outermost transaction is recognized, all inner deals will be approved. In other words, SQL Server basically ignore the COMMIT statement of the inner nest transaction, in exchange for the final approval or recovery of the outer trade to determine the completion of all inner transactions. (There will be further instructions in the real world example in the bottom.) In addition, in the nest, if the outer or any inner trading executes the Rollback statement, all transactions will be restored. In the Rollback statement, use the inner track name is invalid, if you have used, SQL Server will return to the error message. You should use the outermost transaction name in this statement, do not use the name, or use a "SavePoint" name (the storage point will be discussed in the next part).
The real world uses nest trading The following is an example of a neo-transaction using a pre-depressed program. The pre-preservation program includes a display transaction, and this pre-depression is called from another existing transaction. Therefore, the transaction in the pre-stored procedure becomes inner nest. The following program code shows a statement for establishing a pre-deployment program (for convenience, this example uses the Print statement rather than the real data to modify the transaction of the call pre-store.
Use mydb
Go
Create Procedure Place_ORDER - CREATES The Stored Procedure
AS
Begin TRAN PLACE_ORDER_TRAN
Print 'SQL Statements That Perform Order Tasks Go Here'
Commit TRAN PLACE_ORDER_TRAN
Go
Begin TRAN ORDER_TRAN - Begins The Outer Transaction
Print 'Place An ORDER'EXEC Place_Order --Calls The Stored Procedure, Which
- Begins The Inner Transaction
Commit TRAN ORDER_TRAN - Commits The Inner and Outer
Go - TRANSACTIONS
After performing these programs, you will see the output two print statements. The Place_Order_TRAN transaction must have a commistrative statement in the pre-program to mark the end of the transaction, but it is truly recognized when you want to ordered the Order_Tran transaction. Place_Order_TRAN is approved or restored, and it is fully dependent on whether ORDER_TRAN is recognized. Although the SQL Server actually does not recognize the inner trade, it updates the @@ TRANCOUNT system variable for each encountered Commit statement. This variable maintains tracking information of the number of startup transactions connected to each user. When there is no start-up transaction, @@ TRANCOUNT is 0. With the beginning of each transaction (using begin TRAN), the @@TRANCOUNT value is an increment, with the approval of each transaction, the @@TRANCOUNT value is a decrease. When @@ TRANCOUNT reaches 0, recognizes the outermost transaction. If a Rollback statement is performed in some place of the external or inner deal, @@ TRANCOUNT is set to 0. Remember, you should recognize every inner deal, then @@TRANCOUNT will decline correctly. You can detect the value of @@ TRANCOUNT to determine if there is a trading activity. You can see the value of @@ TRANCOUNT using SELECT @@ TRANCOUNT.
Real world Using @@ TRANCOUNT below is an example of @@ TRANCOUNT. Suppose you have a neo-transaction composed of two transactions, which are inner trading and an outer trade, as the above example. Before the two transactions are started, the value of @@ TRANCOUNT is 2 before either transaction approval. Since @@ TRANCOUNT is now non-0 value, the outermost transaction cannot be recognized. When the inner transaction is recognized, the @@TRANCOUNT value dropped to 1. If you execute a commit statement for the outermost transaction, @@ TRANCOUNT drops to 0, and the outer transaction will be recognized. The following program code is similar to the previous example, but we will add statements that capture @@ TRANCOUNT values:
Use mydb
Go
Drop Procedure Place_ORDER
Go
Create Procedure Place_Order - Creats The Stored Procedure.
AS
Begin TRAN PLACE_ORDER_TRAN - TRANCOUNT IS INCREMENTED.
Print 'SQL Statements That Perform Order Tasks Go Here'
SELECT @@ TRANCOUNT AS TRANCOUNT_2
Commit TRAN PLACE_ORDER_TRAN - TRANCOUNT IS DECREMENTED.
Go
SELECT @@Trancount As TRANCOUNT_INITIAL
Begin TRAN ORDER_TRAN - TRANCOUNT IS Incremented.Print 'Place An ORDER'
SELECT @@ TRANCOUNT AS TRANCOUNT_1
Exec place_order --calls the stored procedure,
- Which Begins The Inner Transaction.
SELECT @@ TRANCOUNT AS TRANCOUNT_3
Commit TRAN ORDER_TRAN - TRANCOUNT IS DECRENTED.
SELECT @@ TRANCOUNT AS TRANCOUNT_4
Go
If you do these statements, you will see a range of @@ TRANCOUNT values, which are 0, 1, 2, 1, 0.
Note You must approve each transaction for use in explicit transactions using Begin TRAN. When you use a nest transaction, SQL Server will recognize the outermost transaction after all inner deals will be recognized.
Injection mode In "Implicit Mode", the transaction will automatically start and continue to end the transaction in the case of COMMIT or ROLLBACK statements when using certain T-SQL statements. If the end statement is not specified, the transaction will restore when the user is disconnected. The following statement will start a new transaction in the hidden mode: ALTER TABLE CREATE DELETE DROP FETCH GRANT INSERT OPEN RELETE DROP FETCH GRANT INSERT OPEN Revoke Select Truncate Table Update When one of these statements is used to start an implicit transaction, the transaction will continue until it is clear. The designation ends, even in the transaction, the other of these statements is performed. After the transaction is clearly recognized or restored, one of these statements will be implemented next time, and a new transaction will begin. This program will continue until the hidden mode is turned off. To set an implicit trading mode to ON, you can use the following T-SQL instruction:
Set implicit_transactions {on | OFF}
ON launches this mode, OFF turns off this mode. When the hidden mode is turned off, automatic approval mode will be used next. The implicit mode is useful when you execute the information that you need to protect in the transaction. You can turn on an implicit mode at the beginning of the instruction file, perform the necessary modifications, and then turn off the mode at the end. In order to avoid parallelism, the implicit mode is turned off after data modification and the browsing information. Because if the next statement after an approved is a SELECT statement, a new transaction will begin in the hidden mode, and the resource will be released until SELECT is approved. There are two ways to rehabilitate the recovery of transactions: one is automatically restored by SQL Server, the other is manual regular recovery. In a particular case, SQL Server will restore your transaction. However, you must make your program have logical consistency, you must call the Rollback statement when necessary. Let's take a look at these two methods. Automatic recovery as mentioned before this chapter, if the transaction fails because of a serious error, for example, the transaction has begun to execute the interruption of the network connection, or the user-end application or computer has an error, SQL Server will Automatically restore transactions. Rehabilitation will reply to all modifications caused by the transaction and release all resources used by the transaction. If a "Run-Time" state is incorrect, such as conditional constraints or rule conflicts, SQL Server automatic recovery is only an error statement. To change this behavior, you can use Set XACT_ABORT statements. Start this option indicates that the SQL Server automatically restores the transaction in the execution period error event. This technique is sometimes useful, for example, when a statement in your transaction is caused by violating a foreign key condition; and because the statement fails, you don't want other statements to pass, you can Set XACT_ABORT. In the preset, XACT_ABORT is set to OFF. SQL Server will use automatic recovery during the period of the server replying to the servo. For example, if the transaction is being executed, a serious failure sufficient to make the system reset is happened. When SQL Server is restarted, it will automatically restore (Automatic Recovery). Automatic Recovery includes reading the transaction record file to re-recognize the transaction that has not been written to the disk, and is rehabilized (ROLL BACK) for the transaction that is lost (not yet recognized). Program Rehabilitation Use the Rollback to describe, you can specify the incidence of restoration in the transaction. Rollback statements end transactions and restore all changes made by the transaction. If you trigger recovery in the middle of the transaction, the remainder of the transaction will be ignored. For example, if the transaction is a holistic pre-deployment, Rollback occurs in this pre-depreciation, and the pre-depreciation will restore, and the system will continue to process the next statement in the batch file. If you want to decide whether to restore the transaction according to the number of data recorded in SELECT, you should use the @@ rowcount system variable, @@ rowcount contains the number of data columns returned by query or updated, deletes the number of data columns. If the specified number of data is not compliant, you just simply need to find some or not, if there is any column or the number of numbers in the specified situation, you can use the IF EXISTS statement with the SELECT statement. This statement does not return any information columns, will only pass "TRUE" or "False". If the result is "True", the next statement will be executed; if "false" is back, the next statement will not be executed.
The IF exists can also use the ELSE clause. The following is an example of using if exists ... Else clauses, assuming the following transactions have updated the total number of roysched tabs in the ROYSCHED data sheet in two royalty, but if they are updated, two types of tax rates Don't exist, no UPDATE commands will be performed. Transactions use rollback to confirm this result. Begin TRAN UPDATE_ROYALTY - BEGIN The Transaction.
USE PUBS
If EXISTS (SELECT TITLES.TITLE, ROYSCHED. ROYALTY from Titles,
ROYSCHED
Where titles.title_id = ROYSCHED.TILE_ID
And roysched.royalty = 16)
Update roysched set royalty = 17 where royalty = 16 --13 rows exist.
Else
Rollback TRAN UPDATE_ROYALTY - ROLLBACK IS NOT EXECUTED.
If EXISTS (SELECT TITLES.TITLE, ROYSCHED. ROYALTY from Titles,
ROYSCHED
Where titles.title_id = ROYSCHED.TILE_ID
And roysched.royalty = 15) - NO ROWS EXIST.
Begin
Update roysched set royalty = 20 Where royalty = 15
Commit TRAN UPDATE_ROYALTY
End
Else - ROLLBACK IS EXECUTED.
Rollback TRAN UPDATE_ROYALTY
Go
In this transaction, the first IF EXISTS (SELECT ...) states find some presented information columns, so the first UPDATE command is executed (showing 13 data columns have an impact). The second SELECT describes the 0 data column, so no second UPDATE command is executed, but executes the transaction restoration rultback tranupdate_royalty. Since Rollback replies all modifications to the start of the transaction, the first Update is also restored. If you perform SELECT narrative again, you can still have 13 columns set to 16. The tax rate is set to 17, which is restored by the Rollback statement.
Note Some new keywords in this transaction: IF, else, begin, and end. These keywords will be discussed in detail on Chapter 20.
After the transaction is recognized, you can no longer recover. (Remember, until the outer trading is recognized, the inner transaction is really recognized.) In the case of a single transaction, you must call Rollback before commithanism. In the case of neo-trading, once the outermost transaction is recognized (so the inner transaction is also recognized), no transaction cannot be restored. We have already mentioned that you can't restore the inner deal, but you must restore the entire transaction. Therefore, if you contain a trading name in the Rollback narrative, you want to use the outermost transaction name to avoid confusion, otherwise SQL Server will pass an error message. You can use the "SavePoint" to retain some modifications to avoid restoring the entire transaction. Save Point You can avoid restoring the entire transaction through the "SavePoint", and the storage point can restore from a point in the transaction rather than from the start point of the transaction. All modifications from the beginning to the storage point will remain valid and will not be restored, but the statements performed between the Rollback statement after the storage point will be restored. The statement after Rollback will continue. If you do not specify a storage point, all modifications will return to the start state of the transaction. Note that when the transaction is restored to the storage point, SQL Server does not release the locked resource, and the resource is subject to the transaction approval or the entire transaction will be released. To specify the storage point, the following statements can be used: save tour_name | @savepoint_name_variable}
The location of the storage point in the transaction should be where you want to restore. To recover to the storage point, use the Rollback TRAN statement with the stored point name, as shown below:
Rollback Tran SavePoint_name
After the Rollback statement, you can use more T-SQL description to continue trading. After the first ROLLBACK statement, it is necessary to contain at least one COMMIT narrative or another Rollback narrative, so that the entire transaction can be completed.
Related Information To learn more about the storage point, more information and better use examples, please check the "Save Point" in the series of books in the line, and select "Save Transaction" in the "found topic" dialogue.
Transaction Lock SQL Server uses an object called "Lock" to prevent multiple users from simultaneously modifying the database, and prevents the user from reading the information changed by other users. Lock helps ensure logical integrity of transactions and materials. Lock is managed by SQL Server software internally, and can be obtained based on each user connection. When the user gets (or possesses) the lock of the resource, the lock symbolizes the user's use rights to the resource. Resources that can be locked by the user include information columns, data pages, a range (eight consecutive pages), data sheets, or the entire database. For example, if the user holds a lock to a certain information paging, another user cannot perform operations to the same pagination, otherwise it will affect the job with the locked user. Therefore, the user cannot update the information paging currently being locked and being read by another user. If the lock has been held by a user, another user cannot obtain the lock of the conflict. For example, two users cannot have lockups that update the same pagination at the same time. The same lock cannot be used by more than one user. According to the user action, the lock management of SQL Server can be automatically obtained and released. DBA or programmaker does not need to perform any action to manage lock. However, when specific queries or modifications are performed on the database, you can use the stylized prompt to indicate the type of SQL Server to obtain the lock; these will be discussed in the
The lock hierarchy lock can be obtained by the number of resources; the type of resource determines the locking data point level. Table 19-2 lists the resources that SQL Server can lock, and the hierarchy of the lock data is sorted from small to large.
Table 19-2 Locked resources
Resource Lock Type Description RID (Row ID) Data Listation Level Used to lock the single data column within the information. The index key data list is used to lock a single data column within the index. The page information page is used to lock a single 8kb paging within the data sheet or index. The range range hierarchy is used to lock a range of consecutive groups for an extent, eight data sheets or index. The data sheet is used to lock the entire information list. The library database level is used to lock the entire database.
As the data point level is incremented, the parallelism will drop. For example, locking the entire data table will block other users to access the information sheet. However, due to fewer locking, the system consumption will also decline. As the data point level is decreasing - for example, the paging level or the information column hierarchical lock-parallelism is rising, because more users are allowed to simultaneously access different paging or data columns in the data sheet. In this way, the system consumption will also rise because many data columns or pagins will require more locking when they are accessed. SQL Server automatically selects a lock type that is suitable for this task to minimize lock-locked system consumption. SQL Server also automatically determines the lock mode according to the resource to be locked in accordance with each transaction; these modes will be discussed below. Lock mode "Lock Mode" specifies how parallel users (or parallel transactions) can use resources. In any of these modes, each type of lock can be obtained. There are six types of lockouts: "Shared", "Update", "Exclusive", "Intert)," Structural Description (SCHEMA) "and" Bulk Update ). The shared shared lock mode is used to read the operation, such as the operation performed when using SELECT statements. This mode allows parallel transactions to simultaneously read the same resource, but does not allow any transactions to modify the resource. Once the read is completed, the common lock will be released unless the isolation level is set to a reusable read or higher level, or a lock prompt overwritten the behavior has been specified in the transaction. Update Update Lock Mode when resource updates. Only a resource can have a transaction at a certain time. You can get an update lock. If the transaction does modify (for example, find the information column that conforms to the search criteria), the update lock is converted to exclusive lock (hereinafter will be described below); otherwise, it is converted to a shared lock. Excising exclusive monoliths are used to modify the operations such as update, insert, and delete. When the transaction holds exclusive locks for a resource, no other transactions can read or modify the resource. This lock mode prevents parallel users from updating the same information, as this may cause invalid information. The intention lock mode is used to establish a locking class architecture. For example, the intent lock of the data sheet level Description SQL Server is intended to obtain a paging, several pagination or several data columns in this data sheet. If the transaction needs to obtain exclusive locks of a resource, SQL Server first checks to determine if there is integrated lock on those resources. If the transaction that holds the intent is waiting for those resources, the second transaction cannot obtain exclusive lock. If there is no transaction holding intention to lock and is waiting for those resources, the transaction can obtain exclusive locks of the resource. Three intent locking mode: intention sharing expressed trading intentions in a resource plus shared lock. The intention exclusive expressed transaction intention to add exclusive lock in a certain resource. The intention exclusive sharing expressed trading intention to add shared lock in some resources, while others plus exclusive locks. To learn more about these mode types, please check the "common lock mode" in the series of books in the line, select "Lockens in SQL Server" in the "Locked Topic" dialogue. Structure Description When executing the job described in the data sheet structure, the structure description lock mode is used, such as adding a data line to the data sheet, or the operation of editing queries. Structure Description Locks There are two types, namely "Structure Description Modification (SCH-M)" lock and "Structure Description Netherent (SCH-S)" lock.
"Structure Description Modification" Locks When the Data Definition Language (DDL) is used; "Structure Description Fixability" is locked to compile query (COMILING QUERIES). When compiling queries, other transactions can hold locks in the data table and continue to execute, even exclusive locks, but DDL statements cannot be performed in a data sheet that existing "Structure Description Fixation". A large number of updates a large number of update locks are used in a large number of replication materials to the data sheet, and the "Tablock" prompts that have been specified or using sp_TableOption to set the Table Lock on bulk load information table option. A large number of update locks is to allow the process to copy the data simultaneously to a data sheet, and make the other processes that are not copied in a large amount cannot access the data sheet. Blocking and dead closure (DEADLOCK) may occur in parallel transactions, which will cause more serious problems in the system and make effectiveness and even end operation. These issues can be resolved in the application, and SQL Server will do everything possible to avoid these conditions. This section only makes a description, let you know they and understand their concept. As for the avoidance of blocking and dead knot, the conventional designer is left to complete. The blockade occurs in a transaction to hold a lock, while the second transaction is a lock type that conflicts the same resource requirements. The second transaction must wait for the first transaction to relieve its lock, in other words, it is blocked for the first transaction. The blockade often occurs when the transaction is held for a long time, which causes a series of transactions to be blocked, which are all waiting for other transactions to enable them to obtain the locks required. Figure 19-1 shows an example of a chain blockade. Figure 19-1 Chain Block
The difference between death and blocking transactions is: Dead knots refers to two transactions block and wait. For example, suppose a transaction holds exclusive lock on the data table 1, and another transaction holds exclusive locks on the data sheet 2. Before either exclusive lock is released, the first transaction requires the lock of the data sheet 2, while the second transaction requires the lock of the data sheet 1. Now each transaction is waiting for another exclusive lock, but each transaction has to be approved or restored to complete the transaction, then release its exclusive lock. Both transactions cannot be done because they all require another transaction to control lock to continue trading procedures. Figure 19-2 depicts this situation.
Figure 19-2 Dead knots
Related Information For basic information about how to avoid blockade and death, please search "Block" in the book online, select "Understand and avoid blocking" in the pane under the pane. You can also check the "locking, dead knots" in the roller's book index, and choose "minimize the number of dead knots" and "processing dead knots".
Lock prompts "Locking Hint" is in SELECT, INSERT, UPDATE, and DELETE statements, used to indicate SQL Server to use the correct data table hierarchy lock type T-SQL keyword, these data sheet hierarchical locks A specific narrative. You can use a lock prompt to override the preset transaction isolation level. This technology can only be used in the absolute needs, because if you are not careful enough, it may cause a blockade and dead knot. Let us see where to use the lock prompt useful. Suppose you are using the preset "Read Approve" isolation level for all transactions. Follow the "Read Appreciation" level, when the transaction is read, the resource holds a shared lock until the read is completed, and then the shared lock is released. Therefore, it is assumed that the transaction reads the same data twice, and the results of the read read may be different, as other transactions may be locked and updated the same information. To avoid over-reading problems, you can specify the "serialization" isolation level, but this will cause SQL Server to master all the shared locks needed in all transactions until each transaction is completed. In other words, the SELECT statement specified by a transaction, the shared lock will have been held in the overall transaction. If you don't want to force "serialization" on all your transactions, you can add a lock prompt in the specified query. The HOLDLOCK Locking prompt in the Select Narrator indicates that all the common locks of the SQL Server Controlling the SELECT specified for a transaction until the end of the transaction, no matter what isolation level. Therefore, if the transaction is read, the data will be consistent (not changed for other exchanges). When using the lock prompt, the isolation level of other transactions is unaffected. Note The SQL Server query optimizer automatically determines the most convenient execution scenario. Since SQL Server Query Optimizer automatically selects the correct lock type, the lock prompt is only used by you, and it is absolutely necessary because they may be harmful to parallel homework.
The following list describes the available information table hierarchical locks: Holdlock keeps shared lock until the transaction is completed, not the need to release it when it is no longer needed, information paging, or data columns. Equipped with the serializable lock tips. NOLOCK is only used for SELECT description, and does not accept exclusive locking without submitting a shared lock. This prompt allows reading unauthenticated information (DIRTY read). PAGLOCK can be locked using pagination locations in a single data sheet. READCOMMITTED When the transaction uses "Read Approve" isolation level (SQL Server preset isolation level), use the same lock behavior to perform scanning. ReadPAST is only applied to SELECT, and only uses the information column hierarchical lock to be applied to the locked data column. The transaction is slightly smashed by other exchanges that usually appear in the result set, rather than blocking transactions and waiting for other transactions to release the locks of these data columns. Can only be used in the transaction executed in the "Reading Appraisal" isolation level. Readuncommitted with NOLOCK. REPEATABLEREAD Use the same lock behavior to perform scanning using the same lock behavior when the transaction uses the "overlapping read" isolation level. Rowlock uses the information column hierarchy to lock instead of paging or data table hierarchy. Serializable When the transaction uses the "Serialization" isolation level, use the same lock behavior to perform scan, with Holdlock. Tablock uses the information table level to lock instead of the paging hierarchy lock or the data column level lock. SQL Server controls the lock until the end of the narrative. Tablockx uses exclusive locks to the data sheet. This prompt prevents other trading access data sheets. Updlock reads the data table Use the update lock instead of the shared lock. This prompt allows other users to read data and allow you to update the information, so that there is no other user update information since your last read. You can combine compatible information table tips, such as Tablock and Repeat-Aableread, but you cannot combine conflict tips, such as RepeatableRead and Serializable. To specify the lock prompt of the data sheet, the prompt is included in the parentheses after the T-SQL statement. The following statement is an example of using a TabLockx prompt in SELECT statement: USE PUBS
SELECT Count (ORD_NUM)
From sales (Tablockx)
Where ord_date> "Sep 13 1994"
Go
Tablockx prompt indicates that SQL Server keeps the Exclusive data table hierarchy in the sales data table until the description is completed. This prompt ensures that when the query is queried, there is no other transaction that can be inserted into the Sales data sheet. Due to blocking other trading access data sheets, it may cause other trading waiting and slow response time, and may generate a chain blockade, so be careful about this prompt. Also, you can use the information table to lock the prompt only when absolutely necessary. This chapter summarizes this chapter, we have learned the transaction, including the ACID attribute of the transaction and the different modes used to specify the start and end of the transaction, and also learn SQL Server lock management functions, lock hierarchy and lock mode; we also saw blockade, dead knots And the use of the lock prompt. Now you should quite understand the basic transaction and transaction locking method. In Chapter 20, we will extend on the basis of T-SQL in Chapter 13, you will learn how to use INSERT, UPDATE, and DELETE T-SQL, and others may need when writing to transactions Statement.
