Locking Data in Concurrent Environment and Data Constraints

[ease20022002]

Hi,

I have reviewed a few articles on locking data (record locks, table locks, etc) and on constraints, which appear easier. I am looking for guidance on which level locking I should use in a SQL Server 2005 db and how best to apply constraints to a table.

I am developing a staffing profile (employee time allocator for software development project managers) that could have 10k to 40k records (all employees, offshore, etc.). Each employee can be assigned to multiple projects as far out as 2 years, so 24 * each employee.

As you can imagine, two or more project managers at one time could access one employee and try to allocate that employee to a project at the same time. One employee will have only so much time that can be allocated to a project each month, so one employee will have, at the beginning of the year, two years worth of time to be allocated, which means there will be 24 records for each employee that can be allocated. Obviously, as time passes the records not updateable as they are in the past.

I guess I want to know what is the best level for locking the records when one project manager is accessing one employee. I want the employee locked, not the month.

Also, how easy is it to put a constraint on the allocating field, say hours, so the hours can not exceed 160 in one month (I know hours/month are not constant but it doesn't matter for this example as the metric isn't hours, just using as an example)?

Thank you for any information provided.

 

[NoCoolHandle]

I guess I want to know what is the best level for locking the records when one project manager is accessing one employee. I want the employee locked, not the month.

This tends to indicate that each employee needs their own table. (so add a schema for each emmployee) and then have their project table.

E.G. For [blue]rob[/blue] have a [blue]rob.project[/blue] table.

This would then allow you to have a table lock on the entire table. Depending on the level of access you might place a shared lock on the table when one manager is altering an employees schedule, this would prevent anyone else trying to alter it, but you could also make it exclusive which would lock it completely - eliminating even the chance of a read while one manager is altering the employees projects..

For some of your more complex constraints you might be better off using triggers. (a trigger is like a stored proc and can be used to execute complex logic across tables/database/servers.)

My 1c

Rob

 

[ease20022002]

Thanks for the reply.

I hope you or someone else can provide further assistance.

I am going to easily have to manage 2 thousand to 3 thousand employees. It doesn't seem practical to have 2k to 3k tables related to individual employees. Granted, each table would be small, but wouldn't I be able to use my existing employee, month, project and project management tables and create a fact table with employees, months, allocation (a metric which can't be greater than 1 per month as the constraint), projects, project manager and time stamp to manage each employee?

After thinking about it further, I think I would only want to lock the employee and month combination that the project manager is accessing, and putting a constraint on the allocation field so it can not be greater than 1, which is fully allocated for a month.

Please let me know if the above seems logical and is feasible from a locking perspective.

Thanks,

 

[NoCoolHandle]

You might want to look int

set transaction level repeatable read

then all you would need to do is run a query before updating that covers the employee for that time period and you have the data locked to the point that no one can update/insert into or Delte from what you read.

Rob