Windows Service, Web Service and SQL Server Database Locking

  • Thread starter Thread starter james
  • Start date Start date
J

james

Hi

Apologies if this is the incorrect group for this post.

I have a Windows Service running that populates a database and a Web
Service that accesses the same SQL 2000 database.

One of my tables within the database records the filenames of files
within a number of folders. If a folder changes, all records for that
folder are deleted and a new set of records inserted. This is achieved
by calling an insert record stored procedure a number of times.

The Web Service periodically request a list of files for a particular
folder.

I am keen to prevent the situation that the records are being updated
by the Windows Service while the Web Service is requesting a new set of
records. This would lead to problems.

I would value any suggestions as to the correct approach to resolve
this problem. Should I have a flag somewhere within the database that
says that an update is in progress? Can I lock the table in some way?
Should I forget about the database and store the file list in another
way that prevents this sort of problem from occurring?

Thanks

James
 
I have a Windows Service running that populates a database and a Web
Service that accesses the same SQL 2000 database.
Click to expand...

I am keen to prevent the situation that the records are being updated
by the Windows Service while the Web Service is requesting a new set of
records. This would lead to problems.

I would value any suggestions as to the correct approach to resolve
this problem. Should I have a flag somewhere within the database that
says that an update is in progress? Can I lock the table in some way?
Should I forget about the database and store the file list in another
way that prevents this sort of problem from occurring?
Click to expand...

You should be writing the records within a SQL transaction. Then based on
your record locking scheme (optimistic, pessimistic, etc.), you may need to
handle DBConcurrency exceptions.

You may get better advice over in the m.p.d.framework.adonet newsgroup.

-- Alan
 
Back
Top