Why is optimistic lock efficiency higher than pessimistic lock? (Turn, the article did not give a satisfactory answer)

Add by Zhj: This question did not give a satisfactory answer at the end, I have the same confusion with the questioner. Also, see mysql transaction isolation level, lock

Why is optimistic locking more efficient than pessimistic lock?

Title: "The Collection" Why optimistic locking efficiency is higher than pessimistic lock? Sending station: Drink from (May 19, 2008 12:29:00 Monday), the station letter☆──────────────────────────────────────☆Magiczhang (Lionel Messi, King of the Future)inMay 14, 2008 21:13:24 Wednesday)mention: The book says if it is a long transaction high concurrency so pessimistic lock efficiency is very low, but I have a little doubt can not make sense if several transactions concurrently access the same record: Pessimistic lock is the first access to the transaction success, other transactions waiting for optimistic lock is the first committed transaction success, other transaction rollback is not a success , other failures? What is the difference in efficiency? I'm not sure.☆──────────────────────────────────────☆Caobo (Be a god, create miracles)inMay 14, 2008 21:25:10 Wednesday)mention: Optimistic lock is optimistic that the concurrency caused by the transaction is very low, and with pessimistic lock the whole table, rather than with optimistic lock. In the end, optimistic locking efficiency is high or pessimistic lock efficiency is high, to see the specific application. "In Magiczhang's masterpiece,": "The book says that if it is a long transaction high concurrency so pessimistic lock efficiency is very low, but I have a little doubt really do not think: If a number of transactions concurrently access the same record:: Pessimistic lock is the first access to the transaction success, other transactions wait: Optimistic lock is the first committed transaction succeeds, other transactions are rolled back: Not all is a success, others fail? What is the difference in efficiency? I'm not sure.☆──────────────────────────────────────☆Magiczhang (Lionel Messi, King of the Future)inMay 14, 2008 21:30:28 Wednesday)mention: Pessimistic lock will not lock the entire table, will only lock the record to update it and other transactions as long as not to apply for lock, or can read the lock record, if the other transaction also to modify the record, then even with optimistic lock or only the first committed transaction success, other transactions rollback, then optimistic lock what advantage? "In Caobo's masterpiece,": "Optimistic lock is optimistic that the concurrency caused by the transaction is very low, rather than with pessimistic lock the whole table, rather than: view lock. In the end, optimistic locking efficiency is high or pessimistic lock efficiency is high, to see the specific application. ☆──────────────────────────────────────☆Caobo (Be a god, create miracles)inMay 14, 2008 21:39:18 Wednesday)mention: Lock table or lock record is not up to you, the database will be in the lock table, lock record, or lock part of the record (such as a table into multiple files, it locks a file) select one. The locked record is not necessarily readable, but it depends on the isolation level of the transaction. And the lock itself also accounts for resources. Everything depends on the specific application, depending on what kind of lock is needed for the application. "In Magiczhang's masterpiece,": "Pessimistic locks will not lock the entire table, will only lock the records to be updated it: and other transactions as long as not also apply for lock, or can read the lock record, if other matters also to be repaired.: Record, so even if the optimistic lock or only the first committed transaction success, Other transaction rollback, that optimistic lock has what ...? ☆──────────────────────────────────────☆Caobo (Be a god, create miracles)inMay 14, 2008 21:47:11 Wednesday)mention: Give an example of the teacher in the class, a booking process, first select a ticket, and then book. Obviously, the isolation level of this transaction should be serializable, because the ticket is to be prevented from being duplicated. But once the isolation level is set to serializable, it means that only one person can make a reservation at a time. In fact, many people want to book tickets at the same time, but the level of concurrency is not high, because the probability of booking the same ticket is very low. This time with optimistic lock is obviously better than pessimistic lock. "In Magiczhang's masterpiece,": "Pessimistic locks will not lock the entire table, will only lock the records to be updated it: and other transactions as long as not also apply for lock, or can read the lock record, if other matters also to be repaired.: Record, so even if the optimistic lock or only the first committed transaction success, Other transaction rollback, that optimistic lock has what ...? ☆──────────────────────────────────────☆Magiczhang (Lionel Messi, King of the Future)inMay 14, 2008 22:47:01 Wednesday)mention: Your concept is wrong. The isolation level is set to serializable and pessimistic locks are not the same thing, pessimistic locks are equivalent to select ... for update is generally set to read commited, and then choose pessimistic lock or optimistic lock to resolve unrepeatable read problem transaction 1 Select ... for update, Transaction 2 Light Select does not add for update is OK "in Caobo's masterpiece," said: "For example, a teacher in class, a booking process, first select a ticket, and then book. Obviously, this thing: the isolation level should be serializable, because the ticket is to be prevented from being duplicated. But once set to Serializ: The isolation level means that only one person can make a reservation at a time. In fact, many people have to book tickets at the same time.: The concurrency level is not high, because the probability of the same ticket is very low. This time with optimistic lock is obviously better than pessimistic lock. ☆──────────────────────────────────────☆Volezheng (Mark Williams)inMay 14, 2008 23:19:56 Wednesday)mentioned: There is no research on the database, I guess the meaning of the pessimistic lock, the first thing to be locked, other things to wait, so that other things can not go on, so that the degree of concurrency is very low optimistic lock, we can go on, the degree of concurrency is very high, although finally there can only be a success, But at least there's no need to say that efficiency should mean concurrency? Does that mean? "In Magiczhang's masterpiece,": "The book says that if it is a long transaction high concurrency so pessimistic lock efficiency is very low, but I have a little doubt really do not think: If a number of transactions concurrently access the same record:: Pessimistic lock is the first access to the transaction success, other transactions wait: Optimistic lock is the first committed transaction succeeds, other transactions are rolled back: Not all is a success, others fail? What is the difference in efficiency? I'm not sure.☆──────────────────────────────────────☆Magiczhang (Lionel Messi, King of the Future)inMay 15, 2008 00:25:07 Thursday)mention: Do not wait but finally roll back, it is not worse like you book a ticket, you start to think that the ticket exists, in order to buy this ticket you spent five minutes to fill out the form, and finally found that the ticket is gone, it is not more depressed? Anyway, what's the point of concurrency when only the first commit transaction can commit? "In Volezheng's masterpiece," said: "There is no research on the database, I guess the meaning of it: pessimistic lock, the first thing to be locked, and other transactions to wait, so that other transactions can not be carried out.:, so the concurrency is very low: optimistic lock, we can go on, the degree of concurrency is very high, Although there can only be one success, but to: there are people to wait: the efficiency of the said should be referred to the degree of concurrency it? Does that mean? ☆──────────────────────────────────────☆Volezheng (Mark Williams)inMay 15, 2008 00:53:54 Thursday)mentioned: There may be this situation, a thing has a lot of steps, generally not all the business is successful then the maximum guarantee of concurrency, although it may be sacrificed some of the availability of booking examples, many people may not want to order after a few steps, quit. If it's pessimistic, everyone else has to wait for him to quit. So how should this system be designed? Specific situation specific analysis, I think if I come to design a lot of people using the system, I will use optimism, so at least let everyone will not wait too long "in Magiczhang's masterpiece," said: "Do not wait but finally roll back, it is not worse: like you book a ticket, you start to think that ticket exists, In order to buy this ticket you spent five minutes to fill in the form, the result: found the ticket is gone, it is not more depressed? : Anyway, what's the point of concurrency when only the first commit transaction can be committed? ☆──────────────────────────────────────☆Caobo (Be a god, create miracles)inMay 15, 2008 10:54:09 Thursday)mention: Oh, you misunderstood. I never said serializable and pessimistic locks are the same thing. Second, pessimistic locks include not only the select...for update, which is shown by programmer-specified locks (and this database-specific statement is not supported by all databases). When you start a transaction with an isolation level of serializable, what does the database do with the SELECT statement? It uses a table-level write lock to repel other read and write operations to prevent Phantom reads, which is a pessimistic lock. What you call the Read committed level of pessimistic lock is just a read lock (only the write operation is excluded). For the example I gave you is not available, the isolation level is too low, the occurrence of Phantom read will cause the ticket to repeat the order. Transactions must be started at the level of serializable, implicitly allowing the database to use a table-level, read-Reject lock (high isolation level pessimistic lock). In this case, once a transaction is started and a select is used, the other select must be blocked. The last point, "in fact, is generally set to read commited" is not right, the isolation level of the transaction must be based on the specific application to be determined. The reason we use optimistic locking, or the Read committed level lock that you call programmer-controlled for update, is to reduce the level of isolation to increase the degree of parallelism of transactions, improve performance, and use the method to see the specific application. "In Magiczhang's masterpiece:" Your concept is wrong. : The isolation level is set to serializable and pessimistic locks are not the same thing, pessimistic locks are equivalent to select ... for update: In fact, it is generally set to read commited, then choose pessimistic lock or optimistic lock to solve the UNREPEATABL.: Ad problem: Transaction 1 Select ... for update, Transaction 2 Light Select does not add for update Yes☆──────────────────────────────────────☆Quroger (I'm a wolf from Roma, Shenhua)inMay 15, 2008 11:02:07 Thursdaymention: cb Good Praise ~ "in Caobo (do a god, create miracles) in the masterpiece mentioned:" Oh, you misunderstood. I never said serializable and pessimistic locks are the same thing. Second, pessimistic locks are not just packages: The select...for update is a lock that is displayed by programmers (and this database-specific statement is not: all databases are supported). When you start a transaction with an isolation level of serializable, what does the database do with the SELECT statement? It uses a table-level write lock to repel other read and write operations to prevent Magic: Read, which is a pessimistic lock. : What you call the Read committed level pessimistic lock is just a read lock (excluding write operations). For the example I gave you: the child is not available, the isolation level is too low, the appearance of Phantom reading will cause the ticket to repeat the order. The transaction must be started at the level of serializable: to implicitly let the database use a table-level reject read-write lock (high isolation Level pessimistic lock):. In this case, once a transaction is started and a select is used, the other select must be blocked. : The last point, "in fact, is generally set to read commited" is not right, the isolation level of the transaction must be based on: ... (omitted below)☆──────────────────────────────────────☆Forresty (today, Nimo?) ) inMay 15, 2008 11:34:55 Thursdaymention: "God ~" in Quroger (I was from Roma Wolf, Shenhua Love) in the masterpiece, said: "CB Good ~: ....." (omitted below)☆──────────────────────────────────────☆Magiczhang (Lionel Messi, King of the Future)inMay 15, 2008 12:07:16 Thursday)mentioned: So in what scenario is optimistic lock can improve the concurrency efficiency? If several transactions attempt to modify the same data concurrently, even if the concurrent access or only one can commit success, then what is the significance of this concurrency? "In Caobo's masterpiece," said: "Oh, you misunderstood." I never said serializable and pessimistic locks are the same thing. Second, pessimistic lock does not: include select...for update this is displayed by the programmer-specified lock (and this database-specific language?..: All databases are supported). When you start a transaction with an isolation level of serializable, the.☆──────────────────────────────────────☆Caobo (Be a god, create miracles)inMay 15, 2008 12:22:06 Thursday)I think I'd like to see a specific application. For example, there is a possibility that a number of transactions will have concurrency conflicts, but the probability is small; use optimistic locking to sacrifice a transaction, let it fail and re-execute, in exchange for the concurrent execution of most transactions. "In Magiczhang's masterpiece,": "If a number of transactions are attempting to modify the same data concurrently, even if it is possible to concurrently access or only one can commit successfully." What is the meaning of this concurrency? ☆──────────────────────────────────────☆Caobo (Be a god, create miracles)inMay 15, 2008 12:22:45 Thursday)mention: "You ..." in Forresty's masterpiece, ": God ~☆──────────────────────────────────────☆Programmer (going out of school)inMay 15, 2008 12:31:04 Thursdaymentions: I also come to worship "in forresty (today, Nimo?") In his masterpiece, it says: "God ~☆──────────────────────────────────────☆Forresty (today, Nimo?) ) inMay 15, 2008 12:33:08 Thursdaymentioned: So let's say, suppose a table has 100 records, now there are 10 transactions optimistic lock: 10 transactions at the same time, assuming 2 are hung up because of the conflict, 8 transactions in 1 units of time to complete the pessimistic lock: 10 transactions sequentially, 10 transactions in 10 units of time to complete do not know that there is no understanding error , it's scary ... "in Magiczhang (Lionel Messi, King of the Future):" If a number of transactions are attempting to modify the same data concurrently, even if there is a concurrent access or only one can commit success, then: what is the meaning of this concurrency? ☆──────────────────────────────────────☆Forresty (today, Nimo?) ) inMay 15, 2008 12:34:55 Thursdaymention: You recommend him to MSRA. In the masterpiece of Programmer (to be out of school), I said: "I'll come and worship .☆──────────────────────────────────────☆Programmer (going out of school)inMay 15, 2008 12:36:24 Thursdaymention: ... Who are You "in Forresty (today, Nimo?) ": You recommend him to MSRA," he said. ☆──────────────────────────────────────☆Magiczhang (Lionel Messi, King of the Future)inMay 15, 2008 14:53:01 Thursday)mention: Pessimistic lock does not make 10 transactions in turn, even if the lock, read-only transactions can be accessed concurrently. And pessimistic lock granularity can be very small, only affect the records to be accessed, two transactions can still access different records concurrently but if you want to write the same record must wait, but in this case even with optimistic lock also inevitable conflict "in Forresty's masterpiece," said: Suppose a table has 100 records and now has 10 transactions: Optimistic Lock:☆──────────────────────────────────────☆Magiczhang (Lionel Messi, King of the Future)inMay 15, 2008 15:00:08 Thursday) Mentioned: No, Serializable's grain size is too thick, the whole table is locked in Java persistence with Hibernate said: Most applications don ' t need serializable Isolation (phantom Readsaren ' t usually problematic), and this isolation level tends to scale poorly. Few existing applications use serializable isolation in production, if rather rely on pessimistic locks  s) that effectively force a serialized execution of operations in certain situations. As can see, setting the isolation level is a global option that affects all connections and transactions. From time to time, it's useful to specify a further restrictive lock for a particular transaction. Hibernate and Java persistence rely on optimistic concurrency control, and both allow you to obtain additional locking gua Rantees with version checking and pessimistic locking. From this paragraph can be seen pessimistic lock does not include serializable "in Caobo's masterpiece said:" Oh, you misunderstood. I never said serializable and pessimistic locks are the same thing. Second, pessimistic lock does not: include select...for update this is displayed by the programmer-specified lock (and this database-specific language?..: All databases are supported). When you start a transaction with an isolation level of serializable, ...: SeleCT statement, what will the database do? It uses table-level write locks to repel other read and write operations.: Read, this is a pessimistic lock. : What you call the Read committed level pessimistic lock is just a read lock (excluding write operations). For me to give you.: The child is not available, the isolation level is too low, and the resulting phantom reads will cause a duplicate order of the ticket. The transaction must be started with a level of serializ.: To implicitly let the database use a table-level rejection read-write lock (High isolation level pessimistic:. In this case, once a transaction is started and a select is used, the other select must be blocked. : The last point, "in fact, is generally set to read commited" is not right, the isolation level of the transaction must?..: specific application to set. We use an optimistic lock, or use what you call programmer-controlled for-up: The Read committed level locks are designed to reduce the level of isolation to increase the degree of parallelism in transactions and to improve PE.: Mance, the use of the method is also to see the specific application.

Why is optimistic locking more efficient than pessimistic locks? (Turn, the article did not give a satisfactory answer)