SQL Interview Questions and Answers - Part 19
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 19th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 19
Q171. What is LOCK in SQL? Why locking is important in SQL if it causes performance overheads?
Locking is a mechanism used by the Database Engine to synchronize access by multiple users to the same piece of data at the same time. A lock is a way to ensure data consistency.
SQL Server locks objects when the transaction starts. When the transaction is completed, SQL releases the locked object. This lock mode can be changed according to the SQL process type and isolation level.
Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent. The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock.
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.
Minimizing locks increases concurrency, which can improve performance.
Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. The transaction does this by requesting a lock on the piece of data.
Lock vs RowVersioning:
Locking and row versioning prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time. Without locking or row versioning, queries executed against that data could produce unexpected results by returning data that has not yet been committed in the database.
If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the Database Engine will pause the requesting transaction until the first lock is released.
When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. All locks held by a transaction are released when the transaction completes (either commits or rolls back).
Applications do not typically request locks directly. Locks are managed internally by a part of the Database Engine called the lock manager. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager.
Q172. What is the Locking Hierarchy in SQL? What are the different resources that can have a lock?
SQL Server has a lock hierarchy that acquires lock objects in this hierarchy. The lock hierarchy starts with the database at the highest hierarchy level and down via table and page to the row at the lowest level.
Locks will always be acquired from the top to the bottom as in that way SQL Server is preventing a so-called Race Condition to occur.
Database => Table => Page => Record Row
Essentially, there is always a shared lock on the database level that is imposed whenever a transaction is connected to a database. The shared lock on a database level is imposed to prevent the dropping of the database or restoring a database backup over the database in use.
For example, when a SELECT statement is issued to read some data, a shared lock (S) will be imposed on the database level, an intent shared lock (IS) will be imposed on the table and on the page level, and a shared lock (S) on the row itself
Similarly, in the case of a DML statement (insert, update, delete) a shared lock (S) will be imposed on the database level, an intent exclusive lock (IX) or intent update lock (IU) will be imposed on the table, and on the page level, and an exclusive or update lock (X or U) on the row
Resource Types:
The SQL Server Database Engine has multi-granular locking that allows different types of resources to be locked by a transaction. 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, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. However, it has a lower overhead because fewer locks are being maintained.
The Database Engine often has to acquire locks at multiple levels of granularity to fully protect a resource. This group of locks at multiple levels of granularity is called a lock hierarchy.
Q173. What are the different types of lock modes in SQL?
The SQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.
- Shared Locks (S) Mode
- Exclusive Locks (X) Mode
- Update Locks (U) Mode
- Intent locks (I) Mode
- Schema Locks (Sch) Mode
- Bulk Update Locks (BU) Mode
- Key-Range Locks Mode
Q174. What is Shared Lock (S) in SQL?
Shared Locks(S):
It is used for data read operations that do not change or update data, such as a SELECT statement. This lock type occurs when the object needs to be read.
When a shared lock is imposed, it reserves a page or row to be available only for reading, which means that any other transaction will be prevented to modify the locked record as long as the lock is active. In addition, a shared lock will allow write operations, but no DDL changes will be allowed.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. No other transactions can modify the data while shared (S) locks exist on the resource.
Shared (S) locks on a resource are released as soon as the read operation completes unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
However, a shared lock can be imposed by several transactions at the same time over the same page or row, and in that way, several transactions can share the ability for data reading since the reading process itself will not affect anyhow the actual page or row data.
Q175. What is Exclusive Lock (X) in SQL?
Exclusive Locks (X):
When an exclusive lock is imposed, it will ensure that a page or row will be reserved exclusively for the transaction that imposed the exclusive lock, as long as the transaction holds the lock. When this lock type occurs, it occurs to prevent other transactions to modify or access a locked object.
The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE. 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.
Exclusive (X) lock prevents access to a resource by concurrent transactions. With an exclusive lock, no other transactions can modify data at all. Read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.
An exclusive lock can be imposed on a page or row only if there is no other shared or exclusive lock imposed already on the target. So only one exclusive lock can be imposed on a page or row, and once imposed no other lock can be imposed on locked resources.
Data modification statements, such as INSERT, UPDATE, and DELETE combine both modifications and read operations. The statement first performs read operations to acquire data before performing the required modification operations. Data modification statements, therefore, typically request both shared locks and exclusive locks.
Q176. What is Update Lock (U) in SQL?
Update Locks (U):
An update lock can be imposed on a record that already has a shared lock. It is similar to an exclusive lock (X) but is designed to be more flexible in a way.
An update operation happens basically in two different phases: read phase and write phase. During the read phase, SQL Server does not want other transactions to have access to this object to be changed.
For this reason, SQL Server uses the update lock. Once the transaction that holds the Update Lock (U) is ready to change the data, the update lock (U) will be transformed into an exclusive lock (X).
It is important to understand that the update lock (U) is asymmetrical in regard to shared locks. While the update lock can be imposed on a record that has the shared lock, the shared lock cannot be imposed on the record that already has the update lock.
Update (U) locks prevent a common form of deadlock. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.
Q177. What is Intent Lock (I) in SQL? What are the different types of Intent Lock?
Intent locks (I):
This lock is used by a transaction to inform another transaction about its intention to acquire a lock. The purpose of an intent lock (I) is to ensure data modification to be executed properly by preventing another transaction to acquire a lock on the next in the hierarchy object.
Basically, when a transaction wants to acquire a lock on the row, it will acquire an intent lock on a table, which is a higher hierarchy object. By acquiring the intent lock, the transaction will not allow other transactions to acquire the exclusive lock on that table.
The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy.
Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.
Purpose/Use of Intent Lock:
An intent lock is an important lock type from the performance aspect. Intent locks improve performance because the Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table.
This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table. Intent locks serve two purposes:
- To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
- To improve the efficiency of the Database Engine in detecting lock conflicts at a higher level of granularity.
For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table.
Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page.
Types of Intent Locks:
There are basically two categories of Intent Locks i.e regular intent locks and conversion locks:
1. Regular Intent Locks: There are three sub-types of the Regular Intent Locks in SQL.
Intent Exclusive Lock (IX):
When an intent exclusive lock (IX) is acquired it indicates to SQL Server that the transaction has the intention to modify some of the lower hierarchy resources by acquiring exclusive (X) locks individually on those lower hierarchy resources.
IX lock protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. It also protects requesting shared locks on lower-level resources.
Intent Shared Lock (IS):
When an intent shared lock (IS) is acquired it indicates to SQL Server that the transaction has the intention to read some lower hierarchy resources by acquiring shared locks (S) individually on those resources lower in the hierarchy.
IS lock protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.
Intent Update Lock (IU):
The intent update lock (IU) can be acquired only at the page level and as soon as the update operation takes place, it converts to the intent exclusive lock (IX)
IU lock protects requested or acquired update locks on all resources lower in the hierarchy.
IU locks are used only on page resources. IU locks are converted to IX locks if an update operation takes place.
2. Conversion Intent Locks: There are three sub-types of the Conversion Intent Locks in SQL.
Shared with Intent Exclusive Lock (SIX):
A SIX lock is basically the combination of intent exclusive lock (IX) and exclusive lock (X). So once the transaction acquires a SIX lock on the table, it will acquire an intent exclusive lock (IX) on the modified pages and an exclusive lock (X) on the modified rows.
When the SIX lock is acquired, this lock indicates that the transaction intends to read all resources at a lower hierarchy and thus acquire the shared lock on all resources that are lower in the hierarchy, and in turn, to modify part of those, but not all. For doing so, it will acquire an intent exclusive (IX) lock on those lower hierarchy resources that should be modified.
Only one shared with intent exclusive lock (SIX) can be acquired on a table at a time and it will block other transactions from making updates, but it will not prevent other transactions to read the lower hierarchy resources they can acquire the intent shared (IS) lock on the table.
A SIX lock protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower-level resources. Concurrent Intent Shared (IS) locks at the top-level resource are allowed.
Shared with Intent Update Lock (SIU):
It is a combination of the shared (S) and intent update (IU) locks, as a result of acquiring these locks separately and simultaneously holding both locks.
For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.
Update with Intent Exclusive Lock (UIX):
It is a combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.
When update lock (U) and intent exclusive (IX) locks are acquired at lower hierarchy resources in the table simultaneously, the update with intent exclusive lock (UIX) will be acquired at the table level as a consequence.
Q178. What is Schema Lock (Sch) in SQL?
Schema Locks (Sch):
The SQL Server database engine uses two types of schema locks: Schema Modification lock (Sch-M) and Schema Stability lock (Sch-S).
1. Schema Modification Lock (Sch-M):
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table.
In order to modify a table, a transaction must wait to acquire a Sch-M lock on the target object. Once it acquires the schema modification lock (Sch-M), the transaction can modify the object, and after the modification is completed and the lock will be released.
During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.
ASch-M lock will be acquired when a DDL statement is executed, and it will prevent access to the locked object data as the structure of the object is being changed.
SQL Server allows a single schema modification lock (Sch-M) lock on any locked object.
Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.
The index rebuild is also a table modification process, so it also uses the Sch-M lock on the table. Once the index rebuild ID is issued, a schema modification lock (Sch-M) will be acquired on that table and will be released only after the index rebuild process is completed.
2. Schema Stability Lock (Sch-S):
The Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. The Sch-S locks do not block any transactional locks, including exclusive (X) locks.
Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.
An Sch-S lock will be acquired while a schema-dependent query is being compiled and executed and an execution plan is generated. This particular lock will not block other transactions to access the object data and it is compatible with all lock modes except with the schema modification lock (Sch-M).
Essentially, Schema stability locks will be acquired by every DML and select query to ensure the integrity of the table structure which ensures that table doesn’t change while queries are running.
Q179. What is Bulk Update Lock (BU) in SQL?
Bulk Update Locks (BU):
Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk-loading data from accessing the table. So A bulk update lock is designed to be used by bulk import operations.
When a bulk update lock is acquired, other processes will not be able to access a table during the bulk load execution. However, a bulk update lock will not prevent another bulk load to be processed in parallel. But keep in mind that using TABLOCK on a clustered index table will not allow parallel bulk importing.
The Database Engine uses bulk update (BU) locks only when:
- You use the T-SQL BULK INSERT statement or the OPENROWSET(BULK) function, or you use one of the Bulk Insert API commands such as .NET SqlBulkCopy, OLEDB Fast Load APIs, or the ODBC Bulk Copy APIs to bulk copy data into a table.
- The TABLOCK hint is specified or the table lock on the bulk load table option is set using sp_tableoption.
Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO…SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. This means that you cannot insert rows using parallel insert operations.
Q180. What is the Key Range Lock in SQL?
Key-Range Locks:
Range locks are obtained on index keys to prevent phantoms when you execute transactions under serializable isolation.
A key-range lock is placed on an index, specifying a beginning and ending key value. This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the index.
The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction.
A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.
Key-range locks protect a range of rows implicitly included in a record-set being read by a Transact-SQL statement while using the serializable transaction isolation level.
Key-range locking prevents phantom reads by protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record-set accessed by a transaction.
To Be Continued Part-20...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.