锁定整个数据库？ [英] Lock whole database?

问题描述

我有很奇怪的用户需求。我试图向他们解释有更好的方式来支持他们的业务流程，他们不想听到它。我试图走开，但我首先想看看是否可能有另一种方式。

有任何方式，我可以锁定整个数据库，锁或表锁。我知道我可以把数据库变成单用户模式，但这意味着只有一个人可以一次使用它。我希望很多人能够一次阅读，但只有一个人能够一次写一个。

他们试图做一些真正的

>

• 你想让整个数据库是只读的吗？您绝对可以这样做


• 您要阻止任何新客户端连接到数据库吗？你也可以这样做。

但是真的没有一个数据库锁的概念，只允许一个人使用数据库。至少不是在SQL Server中，不是我知道。

如果你想从这个数据库迁移数据，那么将数据库设置为只读模式（或创建快照

更新：对于您提到的场景（抓取人的数据）与笔记本电脑，然后重新同步），您一定要查看 ADO.NET同步服务 - 这正是它的功能！

即使您不能使用ADO.NET同步服务，您仍然可以选择性地和使用笔记本电脑的更改智能更新中央数据库，而不锁定整个数据库。 SQL Server有几个方法来更新行，即使在数据库正在使用 - - 真的没有必要完全锁定整个数据库只是为了更新几行！

例如：您应该有 TIMESTAMP （或 ROWVERSION ）列，这将很容易让您看到是否发生任何更改。如果TIMESTAMP字段（实际上只是一个计数器 - 它与日期或时间无关）未更改，则该行未更改，因此不需要考虑进行更新。

I have really odd user requirement. I have tried to explain to them there are much better ways of supporting their business process and they don't want to hear it. I am tempted to walk away but I first want to see if maybe there is another way.

Is there any way that I can lock a whole database as opposed to row-lock or table-lock. I know I can perhaps put the database into single-user mode but that means only one person can use it at a time. I would like many people to be able to read at a time but only one person to be able to write to it at a time.

They are trying to do some really odd data migration.

解决方案

What do you want to achieve?

• Do you want to make the whole database read-only? You can definitely do that
• Do you want to prevent any new clients from connecting to the database? You can definitely do that too

But there's really no concept of a "database lock" in terms of only ever allowing one person to use the database. At least not in SQL Server, not that I'm aware of. What good would that make you, anyway?

If you want to do data migration out of this database, then setting the database into read-only mode (or creating a snapshot copy of it) will probably be sufficient and the easiest way to go.

UPDATE: for the scenario you mention (grab the data for people with laptops, and then re-syncronize), you should definitely check out ADO.NET Sync Services - that's exactly what it's made for!

Even if you can't use ADO.NET Sync Services, you should still be able to selectively and intelligently update your central database with the changes from laptops without locking the entire database. SQL Server has several methods to update rows even while the database is in use - there's really no need to completely lock the whole database just to update a few rows!

For instance: you should have a TIMESTAMP (or ROWVERSION) column on each of your data tables, which would easily allow you to see if any changes have occured at all. If the TIMESTAMP field (which is really just a counter - it has nothing to do with date or time) has not changed, the row has not changed and thus doesn't need to be considered for an update.

这篇关于锁定整个数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！