Blocking Read of some rows.
-
I have a requirement that specifies that I need to block the access of a process to some records that are used by another process. This can be easily done by adding a Locked(bit) column and a ProcessID(int) column. The problem appears later in the specification when it says that in the case a process dies the rows locked have to be unlocked. The same must apply if the process is blocked in some way. Another problem is the fact that the login with which these processes run does not have VIEW SERVER STATE permission (nor it can be given that permission) so I can't use sysprocesses view or sp_who procedure. Is there any way around this or another possible solution?
-
I have a requirement that specifies that I need to block the access of a process to some records that are used by another process. This can be easily done by adding a Locked(bit) column and a ProcessID(int) column. The problem appears later in the specification when it says that in the case a process dies the rows locked have to be unlocked. The same must apply if the process is blocked in some way. Another problem is the fact that the login with which these processes run does not have VIEW SERVER STATE permission (nor it can be given that permission) so I can't use sysprocesses view or sp_who procedure. Is there any way around this or another possible solution?
You fail to mention what db you are using. If you use Oracle or DB2 you can
Select for update...There's a similar method for SQL server but it locks whole pages instead of records as far as I know."When did ignorance become a point of view" - Dilbert
-
You fail to mention what db you are using. If you use Oracle or DB2 you can
Select for update...There's a similar method for SQL server but it locks whole pages instead of records as far as I know."When did ignorance become a point of view" - Dilbert
I am sorry, I forgot to mention I use MSSQL Server.
-
I have a requirement that specifies that I need to block the access of a process to some records that are used by another process. This can be easily done by adding a Locked(bit) column and a ProcessID(int) column. The problem appears later in the specification when it says that in the case a process dies the rows locked have to be unlocked. The same must apply if the process is blocked in some way. Another problem is the fact that the login with which these processes run does not have VIEW SERVER STATE permission (nor it can be given that permission) so I can't use sysprocesses view or sp_who procedure. Is there any way around this or another possible solution?
Just another idea. Could you select the data and then delete it from the table. The data would then have to be persisted in memory or perhaps a different table. Once the process is complete and want's to make the data 'visible' again, it would then restore it to the original table. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]