restricting a user from accessing the same records from a table which an application is updating
-
Hi, I have an application where i am selecting some records and updating and giving it back to the database. Now while i am updating these records, if another user is accessing the same table and same records, i want to restrict him to these records. Eg.. If i have selected some records with sr no. 1,2,3,4,5 and currently in a process in updating these records in an application, then i dont want these records to be updated or used by some other user at the same time. Is it possible??
Sandeep Kumbhar
-
Hi, I have an application where i am selecting some records and updating and giving it back to the database. Now while i am updating these records, if another user is accessing the same table and same records, i want to restrict him to these records. Eg.. If i have selected some records with sr no. 1,2,3,4,5 and currently in a process in updating these records in an application, then i dont want these records to be updated or used by some other user at the same time. Is it possible??
Sandeep Kumbhar
can you add a new column in the table which flag if it is being accessed ? and if it is then deny any other request to it. so before updating the data, it will check if the flag is up or down. if it's down, change it to up and modify the data, commit, then change the flag to down. if the flag is up, then do nothing or show a message
-
Hi, I have an application where i am selecting some records and updating and giving it back to the database. Now while i am updating these records, if another user is accessing the same table and same records, i want to restrict him to these records. Eg.. If i have selected some records with sr no. 1,2,3,4,5 and currently in a process in updating these records in an application, then i dont want these records to be updated or used by some other user at the same time. Is it possible??
Sandeep Kumbhar
i read somewhere that you can control record locking using different isolation levels :~
-
Hi, I have an application where i am selecting some records and updating and giving it back to the database. Now while i am updating these records, if another user is accessing the same table and same records, i want to restrict him to these records. Eg.. If i have selected some records with sr no. 1,2,3,4,5 and currently in a process in updating these records in an application, then i dont want these records to be updated or used by some other user at the same time. Is it possible??
Sandeep Kumbhar
By default, while the update is actually taking place nobody will have access to the record - that is known as a dirty read. I suspect what you actually are trying to describe is have user A and user A read the data, user A updates it and then user B updates it, losing user A's changes. This is a common scenario, frequently resolved by using either a timestamp column or a sequence number on the record which is checked before the update is done - if its changed then do not update. Hope this helps
Bob Ashfield Consultants Ltd
-
Hi, I have an application where i am selecting some records and updating and giving it back to the database. Now while i am updating these records, if another user is accessing the same table and same records, i want to restrict him to these records. Eg.. If i have selected some records with sr no. 1,2,3,4,5 and currently in a process in updating these records in an application, then i dont want these records to be updated or used by some other user at the same time. Is it possible??
Sandeep Kumbhar
You can build your own locking strategy with a stored procedure supplied by Microsoft. Check these references out .. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx[^] http://msdn.microsoft.com/en-us/library/ms189823.aspx[^]