Architecture for implementing locking
-
Hi, I have a database used for storing information about our machines. I have tables for machine configuration, utilisation history, schedules etc. I'm currently working on a tool an extension to the database where supervisors will be able to open and view the schedule for a specfic machine, make changes, and save it. The problem is that obviously I don't want two different users to be able to open a schedule at the same time(or both could save and overwrite each others changes), so I need to somehow lock the schedules. What i've thought of doing is including a Lock table, that contains uniqueId's of locked schedules, so before opening a schedule, the lock table is checked and if the schedule is locked, the open is cancelled. Or if the schedule is free, a lock row is inserted and the user is allowed to open the schedule. Problem is, what happens if a user's PC loses power while they have a schedule open. That would mean that the schdeule would remain locked forever. So next i considered adding an ExpiryTime to a lock. So if a user crashed out, the lock would expire after a few minutes. But that means that the app will have to constantly refresh the lock to stop it expiring while the use has the schedule open. I'm not too keen on this idea either. What if the users pc just slowed up and they couldn't refresh the lock in time. I'd then have to handle kicking them out and they'd lose any changes. The other problem is that we also have some wireless laptops, so sometimes, connections to the database are intermittant. How can I implement this so if the connection is lost for a minute or two, the users work isn't? Is there any standard pattern for implementing this kind of thing? It must be a common requirement. Can anyone suggest any ideas or point me in the right direction. Has anyone implemented this kind of system before and can suggest a proven technique? Alternativly, is there functionality within SQL server to implement this kind of thing. I'm not just talking about row locking as a schedule will consist of many jobs, each consisting of many products and the quantities required of each product, so a "schedule" will span multiple tables. Thanks
Simon
-
Hi, I have a database used for storing information about our machines. I have tables for machine configuration, utilisation history, schedules etc. I'm currently working on a tool an extension to the database where supervisors will be able to open and view the schedule for a specfic machine, make changes, and save it. The problem is that obviously I don't want two different users to be able to open a schedule at the same time(or both could save and overwrite each others changes), so I need to somehow lock the schedules. What i've thought of doing is including a Lock table, that contains uniqueId's of locked schedules, so before opening a schedule, the lock table is checked and if the schedule is locked, the open is cancelled. Or if the schedule is free, a lock row is inserted and the user is allowed to open the schedule. Problem is, what happens if a user's PC loses power while they have a schedule open. That would mean that the schdeule would remain locked forever. So next i considered adding an ExpiryTime to a lock. So if a user crashed out, the lock would expire after a few minutes. But that means that the app will have to constantly refresh the lock to stop it expiring while the use has the schedule open. I'm not too keen on this idea either. What if the users pc just slowed up and they couldn't refresh the lock in time. I'd then have to handle kicking them out and they'd lose any changes. The other problem is that we also have some wireless laptops, so sometimes, connections to the database are intermittant. How can I implement this so if the connection is lost for a minute or two, the users work isn't? Is there any standard pattern for implementing this kind of thing? It must be a common requirement. Can anyone suggest any ideas or point me in the right direction. Has anyone implemented this kind of system before and can suggest a proven technique? Alternativly, is there functionality within SQL server to implement this kind of thing. I'm not just talking about row locking as a schedule will consist of many jobs, each consisting of many products and the quantities required of each product, so a "schedule" will span multiple tables. Thanks
Simon
Hi there ! First all sorry about my little english. My native language is spanish. Well, reading carefully your post, seems You must to add support for Managing Locking and Concurrency. (Follows fragments were taken from the Microsoft Best Practices Series) ... Managing Locking and Concurrency Some applications take the “Last in Wins” approach when it comes to updating data in a database. With the “Last in Wins” approach, the database is updated, and no effort is made to compare updates against the original record, potentially overwriting any changes made by other users since the records were last refreshed. However, at times it is important for the application to determine if the data has been changed since it was initially read, before performing the update. Data access logic components implement the code to manage locking and concurrency. There are two ways to manage locking and concurrency: Pessimistic concurrency. A user who reads a row with the intention of updating it establishes a lock on the row in the data source. No one else can change the row until the user releases the lock. Optimistic concurrency. A user does not lock a row when reading it. Other users are free to access the row in the meantime. When a user wants to update a row, the application must determine whether another user has changed the row since it was read. Attempting to update a record that has already been changed causes a concurrency violation. Using Pessimistic Concurrency Pessimistic concurrency is primarily used in environments where there is heavy contention for data, and where the cost of protecting data through locks is less than the cost of rolling back transactions if concurrency conflicts occur. Pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency requires a persistent connection to the database and is not a scalable option when users are interacting with data, because records might be locked for relatively large periods of time. Using Optimistic Concurrency Optimistic concurrency is appropriate in environments where there is low contention for data, or where read-only access to data is required. Optimistic concurrency improves database performance by reducing the amount of locking required, thereby reducing the load on the database server. Optimistic concurrency is used extensively in .NET to address the needs of mobile and disconnected applications, where locking data rows for pro
-
Hi there ! First all sorry about my little english. My native language is spanish. Well, reading carefully your post, seems You must to add support for Managing Locking and Concurrency. (Follows fragments were taken from the Microsoft Best Practices Series) ... Managing Locking and Concurrency Some applications take the “Last in Wins” approach when it comes to updating data in a database. With the “Last in Wins” approach, the database is updated, and no effort is made to compare updates against the original record, potentially overwriting any changes made by other users since the records were last refreshed. However, at times it is important for the application to determine if the data has been changed since it was initially read, before performing the update. Data access logic components implement the code to manage locking and concurrency. There are two ways to manage locking and concurrency: Pessimistic concurrency. A user who reads a row with the intention of updating it establishes a lock on the row in the data source. No one else can change the row until the user releases the lock. Optimistic concurrency. A user does not lock a row when reading it. Other users are free to access the row in the meantime. When a user wants to update a row, the application must determine whether another user has changed the row since it was read. Attempting to update a record that has already been changed causes a concurrency violation. Using Pessimistic Concurrency Pessimistic concurrency is primarily used in environments where there is heavy contention for data, and where the cost of protecting data through locks is less than the cost of rolling back transactions if concurrency conflicts occur. Pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency requires a persistent connection to the database and is not a scalable option when users are interacting with data, because records might be locked for relatively large periods of time. Using Optimistic Concurrency Optimistic concurrency is appropriate in environments where there is low contention for data, or where read-only access to data is required. Optimistic concurrency improves database performance by reducing the amount of locking required, thereby reducing the load on the database server. Optimistic concurrency is used extensively in .NET to address the needs of mobile and disconnected applications, where locking data rows for pro
cmf, Thanks for that. Do you have a link to where thats from? What I want to do is pessamistic locking. Optimistic locking is no good as it would be unacceptable for my users to lose their work if a Concurrency Violation occured when they tried to save. That document says that pessamistic locking is not possible in a disconnected enviroment (like mine). Unfortunately, what I want is pessamistic locking in a disconnected enviroment! I think my idea of using an expiry time on the locks should work well enough for my requirements, as i know that allthough it's disconnected, it will never be a long loss of connection. If anyone can think of better solutions though, I'm open to suggestions.
Simon
-
cmf, Thanks for that. Do you have a link to where thats from? What I want to do is pessamistic locking. Optimistic locking is no good as it would be unacceptable for my users to lose their work if a Concurrency Violation occured when they tried to save. That document says that pessamistic locking is not possible in a disconnected enviroment (like mine). Unfortunately, what I want is pessamistic locking in a disconnected enviroment! I think my idea of using an expiry time on the locks should work well enough for my requirements, as i know that allthough it's disconnected, it will never be a long loss of connection. If anyone can think of better solutions though, I'm open to suggestions.
Simon
Hi once again... Sure You can take a look at http://msdn2.microsoft.com/en-us/library/ms978496.aspx[^] It´s from: Building Distributed Applications Designing Data Tier Components and Passing Data Through Tiers Patterns & Practices Also i found this one source would be interesting: http://www.agiledata.org/essays/concurrencyControl.html[^] I´m start reading also there to refresh some concepts... I see also You need to manage an special lock on resources. Take in mind that pessimistic locking affect scalability. But perhaps the solution deserves it. Hope thoose reading help a bit. cmf. a little DBA !
-
Hi once again... Sure You can take a look at http://msdn2.microsoft.com/en-us/library/ms978496.aspx[^] It´s from: Building Distributed Applications Designing Data Tier Components and Passing Data Through Tiers Patterns & Practices Also i found this one source would be interesting: http://www.agiledata.org/essays/concurrencyControl.html[^] I´m start reading also there to refresh some concepts... I see also You need to manage an special lock on resources. Take in mind that pessimistic locking affect scalability. But perhaps the solution deserves it. Hope thoose reading help a bit. cmf. a little DBA !
Thanks for the help. I've read those links now, and I've got a clearer understanding of how I can implement a solution that fits my requirements. They really helped. Thanks a lot.
Simon
-
Thanks for the help. I've read those links now, and I've got a clearer understanding of how I can implement a solution that fits my requirements. They really helped. Thanks a lot.
Simon