Information on DBMS of Microsoft SQL - Essay Example

Two Phased locking (2PL This technique uses locks on rows so that other users do not access the same data until the lock is released. The feature of this method is that rows can acquire and release when they require. This ensures serializability. The 2PL method has two phases, the growing phase and shrinking phase. Timestamp: This technique assigns a unique timestamp to every row before it begins. When the user or a process tries to access a particular transaction that transaction’s read or write timestamp should be older than the current transaction.

Graph- based: This method imposes a partial ordering on all the data items initially. This order is the order in which the data items are accessed implying that they will be executed in a logical order. Validation-based: This technique uses a validation method to check if there are any conflicts among the users while they are trying to perform the read operation on the rows.Multiversion: In this technique, new version of the row is created whenever any row is updated. The updated row is identified by the ID of the transaction which updates.

Optimistic: This technique displays the record which was read to the user without locking it. Other users can read and modify the record anytime while the main user is performing operations on the output displayed. Types of locks supported: Shared locks allow concurrent users to read data. They are released when the data is read. Exclusive locks do not allow concurrent transactions on the data. Update locks are used to prevent deadlock where if a transaction updates data, it becomes exclusive lock else shared lock.

Intent locks signify which granular level the SQL server wants to acquire lock. Deadlock handling: When a deadlock happens, the SQL server automatically chooses one of process and aborts it. This aborted process rolls back with an error message sent to the user. Log based recovery technique: This technique uses logs to record the updates. The update log defines a single write on the table. This log contains the identifier for the transaction, the data item, the old and the new value updated. Once a transaction performs a write, log record for it is created.

This log helps in recovering the data. Deferred database modification: This method also uses log to record the data but the write action is deferred until the transaction is partially committed. Once the transaction partially commits, the data on the log is executed in deferred writes. In case the system crashes, the data on the log is ignored. Immediate database modification: In this method, the updated table is displayed as the output even when the transaction is in the active state. These modifications in data in its active state are called uncommitted modifications.

Shadow Paging: Two page tables (Current page table and shadow page table) are maintained in this technique. Each time a write occurs in the current page, it points to a page in the disk and a copy of it is made in the shadow table. When a system crashes, data is recovered from shadow table.Checkpoint: It is a point where all the transactions are updated to reflect the data in the log. This updates the data properly to all the transactions ensuring no conflicts occur. This checkpoint ensures that all the data is committed in the SQL Server.

Works cited“Understanding Locking in SQL Server”. N.d. Web. < msdn.microsoft.com > Angus, McDonald. “The Design of Concurrency Control Mechanisms”. September11th 2009.Web. < blogs.cs.st-andrews.ac.uk/angus/tag/concurrency-control-techniques> “Fundamentals of Database Management systems”. N.d. Web. “Database recovery techniques”. N.d. Web. Silberschatz. “Recovery System”. 1997. Web. < www.cs.umbc.edu> Kalman Toth “Optimistic Concurrency Control with rowversion”. August 22, 2010. Web. “Shadow Paging” N.d. Web.

“Checkpoint”. N.d. Web.