Oracle and Microsoft SQL Server have very different locking and isolation strategies. You must consider these differences to ensure application scalability when you convert an application from Oracle to SQL Server.
Oracle uses a multiversion consistency model for all SQL statements that read data, either explicitly or implicitly. In this model, data readers by default neither acquire locks nor wait for other locks to be released before reading rows of data. When a reader requests data that has been changed but not yet committed by other writers, Oracle re-creates the old data by using its rollback segments to reconstruct a snapshot of rows.
Data writers in Oracle request locks on data that is updated, deleted, or inserted. These locks are held until the end of a transaction, and they prevent other users from overwriting uncommitted changes.
Microsoft SQL Server has multigranular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, SQL Server automatically locks resources at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, is expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but has a lower overhead because fewer locks are being maintained. SQL Server can lock these resources (listed in order of increasing granularity).
| Resource | Description |
|---|---|
| RID | Row identifier. Used to individually lock a single row table. |
| Key | Key; a row lock within an index. Used to protect key ranges in serializable transactions. |
| Page | 8-KB data page or index page. |
| Extent | Contiguous group of eight data pages or index pages. |
| Table | Entire table, including all data and indexes. |
| DB | Database. |
SQL Server locks resources with different lock modes that determine how the resources can be accessed by concurrent transactions.
| Lock mode | Description | |
|---|---|---|
| Shared (S) | Used for operations that do not change or update data (read only operations), such as a SELECT statement. | |
| Update (U) | Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are read, locking and then potentially updating resources later. | |
| Exclusive (X) | Used for data-modification operations, such as UPDATE, INSERT, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time. | |
| Intent | Used to establish a lock hierarchy. | |
| Schema | Used when an operation dependent on the schema of a table is executing. There are two types of schema locks: schema stability (Sch-S) and schema modification (Sch-M). | |
It is important in any RDBMS that locks be released quickly to provide the maximum concurrency. You can ensure you are releasing locks quickly by keeping transactions as short as possible. If possible, a transaction should not span multiple round-trips to the server, nor include user “think” time. If you use cursors, you also need to code your application to fetch data quickly, because unfetched data scans can hold share locks at the server and thus block updaters. For more information, see “Using ODBC” later in this chapter.