February 1, 2008 at 11:35 am
Here is the general business requirement:
A section of our application is storing file inventory information for workstation computers. Not all files just files that are tagged a certain way. So for each computer I could get 100-200 files. I also need to store the tag information I mentioned. Each file can be tagged multiple ways.
Currently I have two tables.
•One table to store machine_id, filename, path, size and an is_encrypted bit.
•And a second table to store the tag information (1 record for each tag type) with a foreign key back to the parent file record.
The overall storage seems good. The problems I see involve concurrency while maintaining the data. Attributes of the main file record can change (filesize) or the tags can change. So basically each time the file info comes up we first check to see if it exists, if so remove the data from both tables and readd the new info. I see a lot of contention here and sometimes even deadlocks.
Some additional key points:
•This is a massively concurrent application supporting over 50,000 active users.
•We don’t store history.
•There is much more to the application, this is only one small subject area.
Any thoughts are appreciated.
February 1, 2008 at 12:18 pm
interesting - a volatile table will present problems - I'd personally try to make sure that the table(s) functions as a heap so all new records are added at the top of the table - e.g. no inserts up and down the table - that should make sure that the inserts don't clash with deletes. I'd actually suggest you capture the locking whilst your processes run - it may sound silly but you need to make sure deletes and updates are indexed to ensure there is minimal locking - you must get key locking. You may need to try enforcing row level locking - check the locks during the process to make sure there are no table or page locks. are you using defualt isolation levels?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply