I had a request today to deal with some lock exceptions…rather large numbers of them. With several hundred facilities, 10 data sets, and 12 periods, we ended up with many, and it was not really efficient to use the user interface for this. So, we created some SQL to inject these into the lockexception table. When looking at the table, what is not really clear is why there is a separate primary key (lockexceptionid). Why is there a need to have multiple orgunit/period/dataset combinations here? It would seem that a unique constraint on orgunit/periodid/dataset would be much simpler and enforce some integrity as well.
I had a request today to deal with some lock exceptions…rather large numbers of them. With several hundred facilities, 10 data sets, and 12 periods, we ended up with many, and it was not really efficient to use the user interface for this. So, we created some SQL to inject these into the lockexception table. When looking at the table, what is not really clear is why there is a separate primary key (lockexceptionid). Why is there a need to have multiple orgunit/period/dataset combinations here? It would seem that a unique constraint on orgunit/periodid/dataset would be much simpler and enforce some integrity as well.
I guess so a single key may make things easier, but one could argue that we should have one on the datavalue table in this case?
We have also noticed with large numbers of lock exceptions, the performance of the exception listing is incredibly slow, and leads inevitably to timeouts/pegged CPU usage. Not really sure what that might be related to, but might be good to have a look.
One other idea is to simply exempt specific users from the lock exceptions entirely. This is useful in situations when a certain user may need to be allowed to alter data, but it is not known specifically which time periods/datasets/orgunits they may need to modify. Blueprint?
Regards,
Jason
···
On Fri, Oct 31, 2014 at 12:05 PM, Lars Helge Øverland larshelge@gmail.com wrote:
Hi Jason,
you are right, we actually miss a composite constraint on these three columns. Will add it.
Re primary key, its considered good practice to have a single-column primary key as it makes the programming easier in many ways.
I had a request today to deal with some lock exceptions…rather large numbers of them. With several hundred facilities, 10 data sets, and 12 periods, we ended up with many, and it was not really efficient to use the user interface for this. So, we created some SQL to inject these into the lockexception table. When looking at the table, what is not really clear is why there is a separate primary key (lockexceptionid). Why is there a need to have multiple orgunit/period/dataset combinations here? It would seem that a unique constraint on orgunit/periodid/dataset would be much simpler and enforce some integrity as well.