Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Column Level Locking

 
Ajai
Ranch Hand
Posts: 198
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

Consider a scenario where I have 10 columns in a table.
The first 5 columns can be updated by one group of ppl and last 5 can be updated by another group.Each group will have access only to the fields that belong to them.Within a group I have to ensure that one person of a group should not override the changes made by other person in the same group.
Can anybody suggest how can I accomplish this?

Thanks
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ajai,

I don�t think there is any standard way to share your table columns between different groups, using any kind of container or database settings. Hence you have to do this entire job programmatically. I would recommend you using standard J2EE security and use global roles for assigning privileges to a group or another. At runtime you should be able to decide pretty easily what data one group of users can access or not, using the isUserInRole method for example.
The second problem you face it�s about data integrity and here you have enough solutions. The most reliable one is to use appropriate transaction isolation levels. You might also review the version control design pattern for usescases that span more than one transaction. Of course this is a very high level overview and is entirely up to you to come up with a good design.
Regards.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Valentin Tanase:
I don�t think there is any standard way to share your table columns between different groups, using any kind of container or database settings.


I agree, I'm familiar with row level locking or table locking, but column locking among groups is a non-standard solution that only specific DBMS's would support.

I suggest looking into optimistic row-level locking and see if you can apply it in your current situation since this is the general solution for J2EE applications. Its worlds faster and doesn't drain the system nearly as much as pessamistic locking of specific rows/tables.
[ November 29, 2005: Message edited by: Scott Selikoff ]
 
Ajai
Ranch Hand
Posts: 198
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your replies.

In case of version number pattern ,I will have to keep two version no(for the scenario I mentioned above) in table, each corresponding to one set of columns owned by one group since I want that columns belonging to different groups should be updatatable at the same time . To prohibit two users belonging to same group updating column belonging to the their group at the same time,the version no will be compared.

Am I right?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic