Optimistic locking is a very useful technique, and it works just fine even when using less-strict isolation levels, like Read Committed, or when reads and writes are executed in subsequent database transactions. So, optimistic locking can help you prevent Lost Updates even when using application-level transactions that incorporate the user-think time as well. This is because reads and writes are executed in separate HTTP requests, hence on different database transactions. Without optimistic locking, there is no way this Lost Update would have been caught even if the database transactions used Serializable. Nowadays, over the Internet, we no longer execute reads and writes in the context of the same database transaction, and ACID is no longer sufficient.įor instance, consider the following use case: That's why we still see database systems define terms such as SESSION setting. Relational database systems have emerged in the late 70's early 80's when a client would, typically, connect to a mainframe via a terminal. For this to work, we need to issue the SELECT and read the current version prior to executing the UPDATE or DELETE, as otherwise, we would not know what version value to pass to the WHERE clause or to increment. The version column is incremented every time an UPDATE or DELETE is executed, and it is also used in the WHERE clause of the UPDATE and DELETE statements. This time, we have an additional version column. Optimistic Locking allows the conflict to occur but detects it upon applying Alice's UPDATE as the version has changed. Until Alice releases the read lock, Bob's UPDATE blocks. Only after Alice has committed her transaction and the read lock was released on the account row, Bob UPDATE will resume and apply the change. This is because a write operation requires a write/exclusive lock acquisition, and shared/read locks prevent write/exclusive locks. The database acquires these locks on SQL Server when using Repeatable Read or Serializable.īecause both Alice and Bob have read the account with the PK value of 1, neither of them can change it until one user releases the read lock. In the diagram above, both Alice and Bob will acquire a read lock on the account table row that both users have read. Pessimistic locking achieves this goal by taking a shared or read lock on the account so Bob is prevented from changing the account. In the diagram above we can see that Alice believes she can withdraw 40 from her account but does not realize that Bob has just changed the account balance, and now there are only 20 left in this account. The Lost Update anomaly can happen in the Read Committed isolation level. Now, let's consider the following Lost Update anomaly: Or, you could allow the conflict to occur, but you need to detect it upon committing your transactions, and that's what Optimistic Locking does.You can try to avoid the conflict, and that's what Pessimistic Locking does.When dealing with conflicts, you have two options: This is how distributed transactions using two-phase commit protocols (such as XA or COM+ Transactions) work. The DBMS maintains the locks and allows you to pick the session back up through the TxID. In the latter case you open the transaction with the TxID and then reconnect using that ID. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next. This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. different version to yours) you abort the transaction and the user can re-start it. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit. When you write the record back you filter the update on the version to make sure it's atomic. Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |