 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Record lock while customer is editing
I have been searching for the answer to this one for awhile with no success. In the past applications were written so that only one user could edit a record at a time. This was done through record locks. The record would be locked until the user either cancelled the "edit mode" or saved the changes. In a scenario where two users might have the same customer record open, only one user could switch to "edit mode" at a time. Once the first user saved thier work, a second user could then click edit. This screen would then refresh with the current data reflecting the changes made by the first user. Any user could read the data as it existed before the first user clicked "edit". How is this mechanism achieved when using SQL Server 2005 ? Thanks Dan > In a scenario where two users might have the same customer record open, > only one user could switch to "edit mode" at a time. Once the first user [quoted text clipped - 4 lines] > > How is this mechanism achieved when using SQL Server 2005 ? This technique is called pessimistic concurrency because this assumes that other users are likely to edit the same data. Pessimistic concurrency is generally discouraged because it can lead to concurrency and performance issues. For example, if a user can click edit and then go to lunch (or takes a holiday) and no other user can update the data without intervention. In contrast, an optimistic concurrency technique does not hold locks with the assumption that the usual case is that no one else will change the same data. The application checks to ensure data has not changed upon saving and informs the user in the rare case that it was change by another user. Optimistic concurrency works well in the default READ COMMITTED isolation level is is the widely used. You can implement this pessimistic concurrency in the default READ COMMITTED isolation level by starting a transaction and SELECT the data to be edited WITH (UPDLOCK). SELECT users can still read the data until it is actually modified with an UPDATE statement but a second user that attempts to edit the data (SELECT...WITH (UPDLOCK) will be blocked until the first user commits or rolls back. See the Books Online for details on these transaction isolation levels and concurrency techniques.  SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >I have been searching for the answer to this one for awhile with no >success. In the past applications were written so that only one user could [quoted text clipped - 13 lines] > Thanks > Dan Hi Dan You said: "optimistic concurrency technique does not hold locks " This is a common misconception. It is not true. Optimistic concurrency does hold locks, exactly the same as pessimistic concurrency. The difference is that if another user tries to access the locked data, they will not be blocked, but instead, they will see a version of the data as it was before any changes were made. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com >> In a scenario where two users might have the same customer record open, >> only one user could switch to "edit mode" at a time. Once the first user [quoted text clipped - 46 lines] >> Thanks >> Dan Hi, Kalen. > You said: "optimistic concurrency technique does not hold locks " > [quoted text clipped - 4 lines] > they will not be blocked, but instead, they will see a version of the data > as it was before any changes were made. What I meant by "not hold locks" is that locks are typically held for a much shorter duration with an optimistic concurrency strategy compared with pessimistic concurrency. I think we are both on the same page about lock acquisition and row versioning. I suspect the confusion here is that I am referring to optimistic and pessimistic concurrency as application data access strategies rather than a specific implementation. For example, an application using a pessimistic strategy might begin a transaction and issue a SELECT...WITH (UPDLOCK) when the user clicks Edit. This will of course prevent other users from editing the same row regardless of the isolation level. Whether or not blocking or row versioning occurs depends on the isolation level rather than pessimistic/concurrency strategy. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > Hi Dan > [quoted text clipped - 6 lines] > they will not be blocked, but instead, they will see a version of the data > as it was before any changes were made. Locks are held the same way in either pessimistic or optimistic concurrency. The difference is what will get blocked by those locks. I understand what you mean about the 'strategy' vs the implementation, but there are so many misconceptions about what optimistic concurrency is, that I think we need to be very careful in how we explain the behaviors. You also just said: Whether or not blocking or row versioning depends on the isolation level rather than pessimistic/concurrency strategy. This can be confusing, because row versioning ONLY happens with optimistic concurrency and is really not an issue of isolation level. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com > Hi, Kalen. > [quoted text clipped - 31 lines] >> they will not be blocked, but instead, they will see a version of the >> data as it was before any changes were made. Kalen This is pretty good explained http://msdn2.microsoft.com/en-us/library/aa0416cz(vs.71).aspx > Locks are held the same way in either pessimistic or optimistic > concurrency. [quoted text clipped - 47 lines] >>> they will not be blocked, but instead, they will see a version of the >>> data as it was before any changes were made. > I understand what you mean about the 'strategy' vs the implementation, but > there are so many misconceptions about what optimistic concurrency is, > that I think we need to be very careful in how we explain the behaviors. I just re-read the Locking and Concurrency chapter of your Inside SQL Server 2005: The Storage Engine (a great book, BTW) and it does a thorough job of explaining engine behavior. The problem I have with explaining optimistic/pessimistic concurrency to application developers is that optimistic concurrency can be implemented in the client application independently of the concurrency control used on the server! In fact, the good ole rowversion data type (a.k.a. timestamp) exists specifically to detect update conflicts in applications that use optimistic concurrency management even with with a pessimistic concurrency level like READ COMMITTED on the server. The VS 2003 documentation topic Introduction to Data Concurrency in ADO.NET (http://msdn2.microsoft.com/en-us/library/cs6hb8k4(vs.71).aspx) shows various techniques of how a developer might implement optimistic concurrency. Both were written before the new SQL 2005 isolation levels were introduced. I'm still not sure exactly sure how to best explain optimistic concurrency because it depends on whether I'm referring to the client or server context. Both accomplish the same objectives (minimize blocking locks and detect update conflicts) but the implementation behaviors are different. > You also just said: > Whether or not blocking or row versioning depends on the isolation > level rather than > pessimistic/concurrency strategy. > This can be confusing, because row versioning ONLY happens with optimistic > concurrency and is really not an issue of isolation level. My understanding (perhaps incorrect) is that isolation level and the related database properties determine whether row versioning happens. Is it correct to say that SQL Server optimistic concurrency always uses row versioning (barring cursors/APIs)? SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > Locks are held the same way in either pessimistic or optimistic > concurrency. [quoted text clipped - 47 lines] >>> they will not be blocked, but instead, they will see a version of the >>> data as it was before any changes were made. Hi Dan I agree with most of what you've said. If I'm trying to explain to developers, I try to keep things really high level, and not really even talk about locks at all. I basically explain that optimistic concurrency allows concurrent access but has to check to see if conflicts occur. The 'optimistic' refers to the fact that we're assuming that there usually will NOT be conflicts, but of course, we still have to check for them just in case. For pessimistic, I say we're assuming that conflicts WILL occur, so we prevent problems by not allowing concurrent data access, if the intention is to modify the data. > My understanding (perhaps incorrect) is that isolation level and the > related database properties determine whether row versioning happens. Row versioning happens whenever either of the new database setting is in effect, no matter what isolation level is being used. As soon as either new database property is set, all updates and deletes in any isolation level will start generating versions. Whether or not those versions will be used for reading depends on the isolation level of the session doing the reading. > Is it correct to say that SQL Server optimistic concurrency always uses > row versioning (barring cursors/APIs)? Yes, this seems correct. However instead of the nebulous word 'uses', I would probably say that SQL Server optimistic concurrency is implemented using row versioning. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com >> I understand what you mean about the 'strategy' vs the implementation, >> but there are so many misconceptions about what optimistic concurrency [quoted text clipped - 87 lines] >>>> they will not be blocked, but instead, they will see a version of the >>>> data as it was before any changes were made. Thanks, Kalen. SignatureDan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > Hi Dan > [quoted text clipped - 118 lines] >>>>> data, they will not be blocked, but instead, they will see a version >>>>> of the data as it was before any changes were made. I appreciate the responses to this thread. It seems odd to me that it is the preferred method to use optomistic concurrency where a user spend a long time making changes to the data just to be lost. A good example of this is a itemized list of materials on an invoice. The user would add each line to the invoice and after completing the entire invoice click Save. Depending on the size of the invoice, this could easily take 20 to 30 minutes on large invoices. All of this effort would be wasted when the user clicks save, just because someone else went in and was allowed to change the invoice date. Thanks again, Dan > Thanks, Kalen. > [quoted text clipped - 120 lines] >>>>>> data, they will not be blocked, but instead, they will see a version >>>>>> of the data as it was before any changes were made. I certainly wouldn't say that optimistic concurrency is the preferred method in this case. SignatureHTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://DVD.kalendelaney.com >I appreciate the responses to this thread. It seems odd to me that it >is the preferred method to use optomistic concurrency where a user spend a [quoted text clipped - 134 lines] >>>>>>> data, they will not be blocked, but instead, they will see a version >>>>>>> of the data as it was before any changes were made. I have not been able to find a good resource or reference that will help me with my scenario I mentioned. I would have to imagine that this is a very common problem with application software such as mine. I did find a few approaches that were interesting. Some people have suggested "locking" the record myself. Adding a field to the database to mark it locked. Others have suggested creating a separate table for this purpose, where I add/delete records from this separate table as I lock/unlock them. This table could be used as a control point for all of my tables within my database. I believe both of these approaches could work, but it does not seem to be the "best practice" I have one more thing to add to the scenario, and your advise would be greatly appreciated. As items are used on these invoices, the quantity in stock should be reduced. It the line is deleted from the invoice, the quantity should be returned to stock. In a scenario where multiple line items are on an invoice, each pointing to records in a inventory table, how can you be certain that the stock counts are correct? The quantity in stock on a particular inventory part will change as several different users are using the item. These user would be working on different invoices, but the inventory item is shared for all of them. Obviously these changes are not made until the user chooses to save the invoice. And SQL will lock the records very briefly as the transaction occurs. I imagine manually starting a transaction before any changes are committed is the preferred method. If I am on the wrong path, please point me in the right direction. Thanks Dan Tallent >I certainly wouldn't say that optimistic concurrency is the preferred >method in this case. [quoted text clipped - 139 lines] >>>>>>>> data, they will not be blocked, but instead, they will see a >>>>>>>> version of the data as it was before any changes were made. >I have not been able to find a good resource or reference that will help me >with my scenario I mentioned. I would have to imagine that this is a [quoted text clipped - 6 lines] >database. I believe both of these approaches could work, but it does >not seem to be the "best practice" Frankly, I don't think there is a single best practice when it comes to concurrency. Each of the pessimistic techniques you mention here have merit and there are others as well (e.g. sp_getapplock). A couple of books that describe concurrency techniques in some detail include: Inside Microsoft SQL Server 2005: T-SQL Programming Expert SQL Server 2005 Development. > I have one more thing to add to the scenario, and your advise would be > greatly appreciated. As items are used on these invoices, the quantity [quoted text clipped - 14 lines] > preferred method. If I am on the wrong path, please point me in the > right direction. Yes, I think an explict transaction during invoice save is the only sure way to guarantee proper inventory. You'll also need to make sure updates are done in the same order so that deadlock likelihood is minimized. Note that index and query tuning are especially important here since performance plays an important role in maximizing concurrency. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >I have not been able to find a good resource or reference that will help me >with my scenario I mentioned. I would have to imagine that this is a [quoted text clipped - 175 lines] >>>>>>>>> data, they will not be blocked, but instead, they will see a >>>>>>>>> version of the data as it was before any changes were made. > A good example of this is a itemized list of materials on an invoice. > The user would add each line to the invoice and after completing the > entire invoice click Save. Depending on the size of the invoice, this > could easily take 20 to 30 minutes on large invoices. All of this > effort would be wasted when the user clicks save, just because someone > else went in and was allowed to change the invoice date. The amount of redo effort is is certainly an important factor in the decision to use an optimistic vs. pessimistic concurrency technique. Another important consideration is the likelihood of a conflicting update occurring to the same data (hence the terms optimistic and pessimistic). If this sort of scenario is expected often, pessimistic concurrency may very well be the best choice. But in practice, what is the likelihood that two different clerks will actually work on the same invoice? If the business workflow is such that this scenario should not happen in practice, it may be best to go with optimistic concurrency. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >I appreciate the responses to this thread. It seems odd to me that it >is the preferred method to use optomistic concurrency where a user spend a [quoted text clipped - 134 lines] >>>>>>> data, they will not be blocked, but instead, they will see a version >>>>>>> of the data as it was before any changes were made. Two users changing the same Invoice at the same time may not happen often, but there are plenty of places where shared data can occur. The example I gave earlier in this thread was that several different invoices can have the same inventory item used. In this case the common Inventory record needs modified to update the quantity in stock. This is a very common scenario in my application. Thanks Dan >> A good example of this is a itemized list of materials on an invoice. The >> user would add each line to the invoice and after completing the entire [quoted text clipped - 154 lines] >>>>>>>> data, they will not be blocked, but instead, they will see a >>>>>>>> version of the data as it was before any changes were made. But this is a case where you should use optimistic locking. If a user brings up an invoice with 15 inventory items on it, you DON'T want to lock out every other user from those 15 inventory items for as you said "20 to 30 minutes". If you have more than one or two users, you will have many users waiting long periods of time for those rows to be unlocked. So here I would typically use a form of optimistic locking. I don't care whether or not someone else changed the inventory record between the time I retrieved it and the time I updated it, as long as there is still enough quantity in stock when I do the update. So if when I bring up an order, there is 100 units available of item A and I want to order 30 of them, I don't care that when I was editing this order we recieved 20 more and shipped 50, so there is now 70 on hand. I just subtract 30 and update the inventory item to show we now have 40 on hand. The only problem comes if so many were removed by other processes, that while there was enough to satisify my changed order amount when I retrieved the order, there is not enough when I update the order. For example, there was 100, I want 30, but 80 have been shipped by the time I update the order. But even then you don't throw away all of my work and make me redo everything. Typically, you would, depending on your business requirements, either automatically backorder the item, or ask me if I wanted to backorder the item. But there will always be tradeoffs between the concurrancy of multiple users changing the data, the integrity of the data, and the possibility that the underlying data may change while a user is looking at it. But typically in an Order Entry/Invoicing system you want to use some form of optimistic locking for inventory quantities. I guarentee you that when I go to Amazon.com and bring up the latest best seller, Amazon does not lock out every other customer in the world while I am considering whether or not I want 1 copy or 2 copies. Tom > Two users changing the same Invoice at the same time may not happen often, > but there are plenty of places where shared data can occur. The example [quoted text clipped - 168 lines] >>>>>>>>> data, they will not be blocked, but instead, they will see a >>>>>>>>> version of the data as it was before any changes were made. I appreciate your response and agree completely with what you have stated. The only interesting issue I have is that lets say two people are allowed to modify the same invoice. Lets say there an inventory item ABC with 100 in stock. The current quantity of item ABC on this invoice is 10. Now User1 reads the data and starts working with in the invoice. Now before User1 saves, User2 opens this same invoice. When User1 changes the Quantity from 10 to 6 and saves, the inventory stock count will increase to 104. When User2 changes this quantity from 10 to 6 and saves, the inventory stock count will increase again to 108. Any thoughts on how to protect this from happening... I know alot of people believe you won't have two users in the same invoice, but I guarantee you this.. smaller companies + impantient people = exactly this scenario. Thanks again, Dan > But this is a case where you should use optimistic locking. If a user > brings up an invoice with 15 inventory items on it, you DON'T want to lock [quoted text clipped - 205 lines] >>>>>>>>>> data, they will not be blocked, but instead, they will see a >>>>>>>>>> version of the data as it was before any changes were made. > Any thoughts on how to protect this from happening... I know alot of > people believe you won't have two users in the same invoice, but I > guarantee you this.. smaller companies + impantient people = exactly this > scenario. The example below shows how you might use a rowversion column to protect data integrity. SET XACT_ABORT ON BEGIN TRAN --adjust quantity on hand UPDATE dbo.Products SET QuantityOnHand = QuantityOnHand + (@OriginalInvoiceQuantity - @NewInvoiceQuantity) WHERE ProductId = @ProductId --change invoice item UPDATE dbo.InvoiceLineItems SET Quantity = @NewInvoiceQuantity WHERE InvoiceID = @InvoiceID AND InvoiceLineNumber = @InvoiceLineNumber AND version = @version --version value of initial read row IF @@ROWCOUNT = 0 BEGIN --row was either deleted or changed --rollback inventory change and raise error ROLLBACK RAISERROR ('Invoice line item was changed by another user.', 16, 1) END ELSE BEGIN COMMIT END SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >I appreciate your response and agree completely with what you have stated. > [quoted text clipped - 232 lines] >>>>>>>>>>> see a version of the data as it was before any changes were >>>>>>>>>>> made. In most cases, I would be using some form of optimistic locking on the invoices, so in the senario you described, User2's changes would be rejected. The code Dan gave you shows how. This will irritate User2 and is a cost to your company when User2 must redo some work. But, in most cases in my experience, it happens rarely enough that this cost is small enough that optimistic locking would be the way to go. But if you decide that the cost of that is too high, then use some form of pessimistic lock. In that case, User1 would lock the invoice, then User2 would be prevented from retrieving that invoice until the invoice was updated by User1 and the lock freed (or the lock was freed by some other process). But pessimistic locks have their own costs. For example, you usually need to do extra programming. Like there usually must be some mechanism to free the lock if User1 gets a lock, starts to work on that invoice, and is interrupted and forgets to to unlock the invoice before handling the interruption. In a case like that you usually don't want User2 to have to wait indefinitely to be able to get a lock on that invoice. So it's often additional work to set pessimistic locking up, but it certainly can be done. Like many things in life, it's a tradeoff, and the only correct general answer for which you use is "it depends" <grin>. Tom >I appreciate your response and agree completely with what you have stated. > [quoted text clipped - 232 lines] >>>>>>>>>>> see a version of the data as it was before any changes were >>>>>>>>>>> made. I really appreciate all the help. I think the method I am going to go with is optomistic locking. This seems to deal with the problem I have with inventory quantity changing without locking everyone else out of using that item. In addition I think any changes that will be written to update the inventory quantity will always be based on the difference of the original value and the new value. This takes care of the scenario where user1 and user2 are both using the same inventory item. The code that Dan provided uses this method. I also plan to do this when a user is updating the count directly in inventory. An example of this would be if an item in inventory has the Original value of 10 and we discovered that we only have 8 on the shelf... the difference would be 2. While User1 was making this change to inventory, User2 was to add 1 of this item to an invoice. The inventory stock count would now be 9. When User1 finally saves, the quantity in stock will reflect 7. This would be correct. I will add "manual" locks to solve my problem where 2 users can modify the same invoice at the same time. I can use a method of adding a field to the invoice table for this purpose. As long as all of my routines attempt to "lock" the invoice before allowing the user to edit, this solves this problem as well. Again, I was to thank everyone that helped me get here. Thanks Dan Tallent > In most cases, I would be using some form of optimistic locking on the > invoices, so in the senario you described, User2's changes would be [quoted text clipped - 258 lines] >>>>>>>>>>>> see a version of the data as it was before any changes were >>>>>>>>>>>> made. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.