Updating a database while other users are logged in

Question

Hi

I have the classic problem of an adminstrator wishing to update a database (SQL Server 2008, SP3  but it could be Access 97) with new data, while other users are logged in. The other users are generating reports (so effectively using the database in read-only mode). The adminstrator is uploading data for today (so new data) + amendments for previous days.

What I am interested in is white papers, algorithms, interprocess communication, strategies for handling this situation.
It must be common yet I have not seen anything in the way of stategy.

One strategy is to throw the users off if the administator wishes to do an update, they are allowed on afterwards.
But this is not ideal.

Thanks

IdentityWrapper

---

IdentityWrapper

Answer 1

What I am interested in is white papers, algorithms, interprocess communication, strategies for handling this situation.
It must be common yet I have not seen anything in the way of stategy.

Hello,

I don't understand, what's exactly the problem here? RDBMS can handle concurrency access and depending on the access you could update the data (partly) while an other user runs reports; e.g. if snapshot isolation level is used.

---

Olaf Helper

Blog Xing

Answer 2

Hi Identity,

SQL Server using isolation levels to archive some concurrency situation, it Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.

Here are some references for more details:

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Understanding Locking in SQL Server

http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

Concurrency Series: Basics of Transaction Isolation Levels

http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx

Database Concurrency and Row Level Versioning in SQL Server 2005

http://msdn.microsoft.com/en-us/library/cc917674.aspx

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.