Menu
I want to make sure my database (SQL Server 2008) remains immutable, but still accessible for reads during some maintenance operations (backups, transitions to other DB servers etc), how can I programmatically lock it for changes in C#?
939
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.
An UPDATE lock is a read lock which means "I intend to update this row/page/table". Readers are not blocked by this lock, but only one process can hold an UPDATE lock on a resource. please simulate a scenario which show where update lock play a key role because we know sql server place a lock when update data.
Master Key—this is an additional, optional key in each SQL Server database, which can be protected by the service key or by a secure password provided by the user.
You can execute an ALTER DATABASE <mydb> SET READ_ONLY to put a database in read-only mode.
If you want the command to fail if it can't be executed immediately you specify it like this:
ALTER DATABASE <mydb>
SET READ_ONLY
WITH NO_WAIT
If you want to make it rollback all open connections you specify the command like this:
ALTER DATABASE <mydb>
SET READ_ONLY
WITH ROLLBACK IMMEDIATE
These options are well documented in SQL Server Books online
154
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
