DB2 isolation level IBM saw recently in the official website of the guide, not exclusive, posted out to share with you, Roger E. The directory is. Sanders prepared, the author also wrote the "overall DB2 SQL database developers" and the "Guide to DB2 database API developers overall Guide" books. If the text is suspected of copyright infringement, please delete it KING bars. When many users visit the same phenomenon will occur database The use of single-user database, each branch are sequential implementation, and other services without having to deal with the conflict. However, in multi-user database environment, many things can be implemented simultaneously, and have affairs with each other's business operations are in conflict. In the multi-user environment, if they would not be isolated to one another, four phenomena will occur : The incident happened in the loss of two panels : This update read and try to update the same data, which will update a loss. For example, Service 1 and Service 2 : reading the same line data and the data which were obtained based on the calculated value of the new. If the value of one of its new update of the trip, the Service also updated the same line 2, Service 1 on the implementation of the updated operating lost. As the design, the overall DB2 database is not allowed, such phenomena. Read yet read : dirty data submitted by the Panel, such incidents will occur. For example, one change in his party : Service data, whereas two changes in the panel submitted before reading one of the trip has changed. If the one rollback of the changes, the Panel is not that the two will be reading the data. When plots of the time : not a single reading the same line twice, but each was different data values, such incidents will occur. For example : one to read a line of data services, whereas the change or deletion of the two firms submitted a change. When the Panel again tried to read one of the tour, it would have access to different data values (if the bank has been updated), or found that the bank no longer exists (if the bank were to delete any). Huan Xiang : When the initial conditions not seen a search and matching of data, and that another operation at a later time to see the tour, such incidents will occur. For example, one reading : Panel to meet certain conditions of a data search firms, whereas two search terms into the match with one of the new services firms. If the one originally rally inquiries have implemented, it will have access to the rally. Maintain database consistency and data integrity, but also the same data to allow a number of applications, such as concurrency characteristics. DB2 database overall with the attempt to enforce one of the ways is through the use of isolation level, it decided in a single visit to the data, how to lock or other services firms use the data isolation. The overall level DB2 database used to enforce segregation with the following : Plots can be read (Repeatable Read) Reading stability (Read Stability) Cursor Stability (Cursor Stability) Not the time (Uncommitted Read) "Plots can be read" isolation level When the isolation level at the time the use of the plots, identified during the implementation of services in a single quoted by the Panel of all trades. Using this isolation level, the same issue repeatedly with a SELECT Language Services will always have the same results; Lost updated and dirty time, the plots can not read, Huan Xiang will happen. Reading the use of plots of isolation the same level of services can be retrieved repeatedly rally, and they can implement random operation until terminated by submitting or rollback operation; Executive barring other insert, update or delete operation, which will operate services exist in isolation during the rally are being affected. In order to ensure that "the time plots can be" isolated operating under firm-level data will not be affected by the visit of the negative impact of other services, identified the firms cited the isolation of each trip - and not just by locking the actual retrieval and / or modification of those firms. Therefore, if a panel of 1,000 scanning search firms but only 10 firms, which scan 1000 (the trip and will not be retrieved 10) will be locked. So in reality this isolation level is the work environment? Assume you have a large hotel, and a Web site, which according to a "first come first serve" principle customer acceptance of booking. If your hotel reservation application of the "plots can be read," operating under isolation level, when a client retrieval date of the list of all available rooms, you will not be able to change in those rooms designated range of fees, while other customers will not be able to change or cancel the booking list. Services generated the list until terminated. (For the first customer outside the scope of any inquiry designated rooms, you can change prices or other customers may cancel booking. ) "Reading stability" isolation level Reading stability when using the isolation level, in a single Affairs, the firm will lock in the retrieval of all trades. When using this isolation level, until isolation prior to the termination of services, and other services can not be changed to read all the isolation Services Bank. In addition, other changes in the affairs of other firms in operation prior to submission for the "Reading stability" in the affairs under isolation level it is not visible. Therefore, the use of "Reading stability" isolation level, to the same phrase repeatedly issued SELECT may produce different results. Lost updated, dirty plots time and the time will not occur; However, there may Huan Xiang. "May plots reading" isolation level, the isolation of each transportation services under lock; However, "reading stability" isolation level, the actual retrieval services only locked isolation and / or modify the trip. Therefore, if a panel of 1,000 scanning search firms but only 10 firms, only 10 firms were retrieved (by scanning instead of the 1,000 firms) are being locked up. So, how will this change the hotel reservation isolation-level application program work? Now, when a customer search of a date list of all available rooms, you can change any of the hotel rooms of the houses, and other customers could be abolished in a customer inquiries within the specified date of booking room reservations. Thus, if the termination of the author generated list again before inquiry panel, the new list may contain the new prices and not on the list or have a room. "Vernier stability" isolation level Cursor stability when using the isolation level, as long as the firm isolated location in a transportation Vernier, we will lock the vernier quoted this line. Has been effective in obtaining a lock, until the re-positioning Vernier (usually by calling FETCH words) or segregation Services terminated. Therefore, when using this isolation level, to the same phrase repeatedly issued SELECT may produce different results. Lost no time updating and dirty; But there may not have the time and Huanxiang plots. When the use of "Cursor stability" isolation level panel to be updated Vernier trip from the table to retrieve the Cursor position in the transportation, other services can update or delete the bank. However, if the index is not to be identified own visit, other services can be added to form the new firms, and firms located in the anterior and posterior lock update and / or delete operations. In addition, if amended its retrieval services to the isolation of any trip, in isolation prior to the termination of services, even in Vernier no longer be amended at this trip, other services can update or delete the bank. In other transportation services for other changes, before submitting it for the use of "Cursor stability," the affairs of the isolation level is not visible. Default circumstances that most use "Vernier stability" isolation level. This level of the hotel reservation application isolation, what impact? Now, when a customer search of a date for the list of all available room, and then check on the list produced each room information (check each room), you can change any of the hotel rooms of the houses and other customers of any date of any rooms or cancel reservations; The only exception is the first client ongoing access to the room. When customers see a list of information in another room for the new rooms is the same; You can change a room's customers have access to the prices of other clients can book the room, but not a customer of these ongoing access to the operating room. "Not the time" isolation level At the time did not use the level of segregation, when a single trip retrieval services only when another attempt to delete or modify services firms located in the table being retrieved when they are locked during these matters in a single trip. In the use of this isolation level, the trip is usually not locked maintain, update loss, dirty read, and read Huanxiang plots are not likely to happen. In most cases, the changes made to introduce other services, or rollbacks before submission for the use of "non-reading" can be seen in the affairs of the isolation level. However, such matters could not see or visit other firms to create tables, indexes, or views, until those matters have been submitted so far. Similarly, if other services Delete the existing forms, views, or the index, the use of the term "not the time" for the deletion of isolation-level panel to operate only when the termination of services in order to understand the situation. Such acts are an exception : When operating in "non-reading" level of isolation can be used to update Vernier affairs, and the affairs of the "Cursor stability" under confinement - level operation, and application of the "Cursor stability" isolation level constraints. "Not the time" for those who usually visit the isolation level CD-scale services and / or implementation of certain expressions in the SELECT Services, which did not submit data on the expression of other services without adverse effects. So this level of the hotel reservation application isolation, what impact? Now, when a customer search of a date list of all available rooms, you can change any of the hotel rooms of the houses, and also to any other customer of the date of any rooms or cancel reservations. In addition, if other customers canceled bookings, its business will be terminated if they do not cancel those submitted to the database, which can contain a list generated by the cancellation of booking a room. Designated isolation level Despite the isolation level is how to control the affairs of locking resources, but the fact is, they are designated in the application program level. For embedded SQL applications, in the pre-compiler or to bind to database applications to use the designated isolation level. In most cases, the application is supported by the isolation-level language compiler (such as C or C + +), as ordered by PRECOMPILE PROGRAM /API BIND and the option to set up isolation ISOLATION level. Call for class Adapter (CLI) applications, using the isolation level is specified in the application program running through the redeployment of SQLSetConnectAttr SQL_ATTR_TXN_ISOLATION link attribute () function is installed. Also be set up to the value specified keywords TXNISOLATION CLI application isolation level, the db2cli.ini keyword in the configuration file. For JDBC and SQLJ applications, application isolation level is running through the redeployment presence in the joints connecting the java.sql setTransactionIsolation () method installed. When the isolation level not specified, the default is to use the "Cursor stability" isolation level. For the command line processor (CLP) and the implementation of the orders and Embedded SQL script, CLI, JDBC and SQLJ applications are. 因此，也可以指定从 CLP 运行的命令和脚本所用的隔离级别；在这种情况下，所用的隔离级别是通过在建立与数据库的连接之前在 CLP 中执行 CHANGE ISOLATION 命令设置的。 Select appropriate level of segregation The appropriate choice for the isolation level is very important. Isolation-level support not only affects how well the database concurrency; Services include the impact of the application procedures and the overall performance. This is because the resources required for the acquisition and release locks isolation level to another. Usually, the more strict isolation level of support provided by fewer complications, and the overall performance may be lower because more resources to the acquisition and possession. However, you will have to determine the best use of the isolation level, which should identify the phenomenon acceptable and unacceptable phenomenon to decision-making. The following inference can be used to help you determine the specific environment in which the use of isolation level : If you are performing CD database inquiries, inquiries are being implemented without considering whether or not to return value to the data, the use of "non-reading" isolation level. (Read needs-not higher stability of the data. ) If you do not want to see the value of the data submitted did not get the most complicated cases, the use of "Cursor stability" isolation level. (Need to be reading and services-do not need high data stability. ) If you want complicated, and hoped that the limit of individual firms to maintain stability during the implementation of services, the use of "Reading stability" isolation level. (Or CD is required reading and services-requires a higher level of stability data. ) If you are implementing inquiries, and does not want to see the result of a change in data collection, the use of "plots can be read" isolation level. (Read is the need - the need for high data stability. ) DB2 isolation level Good! DB2 isolation level Good article, we proposed a good look! [ Closed window ]