Database Lock and its types

Question

Hi!!

What does a lock mean in Database?

Answer 1

Database lock basically signifies the transaction about the current status of the data item i.e. whether that data is being used by other transactions or not at that point of time.

Two types of Database lock are present:

• Shared Lock 
• Exclusive Lock

Comments

Ok... But table level lock is different from exclusive lock, I presume.

---

Hey, @ Sudhir,

This is regarding your statement:

The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive table lock on the specified table. The table lock lasts until the end of the current transaction.

When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data.

This lock remains in place until the transaction holding the lock issues a commit or rollback. Table-level locking lowers concurrency in a multi-user system.

Answer 2

Hello Dataking99,

• A database lock is used to “lock” some data in a database so that only one database user/session may update that particular data. 
• So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time.
•  When data is locked, then that means that another database session can NOT update that data until the lock is released .

Database locks can actually be placed at different levels – also known as lock granularity – within the database. Here is the list of lock

1. Database level locking- Entire database is locked – which means that only one database session can apply any updates to the database.
2. File level locking- An entire database file is locked(file can have a wide variety of data – inside a file there could be an entire table, a part of a table, or even parts of different tables.)
3. Table level locking- It means that an entire table is locked as a whole. This lock level comes in handy when making a change that affects an entire table, like updating all the rows in a table, or modifiying the table to add or remove columns.
4. Page or block level locking-Block, or page, level locking occurs when a block or page that is part of a database file is locked
5. Column level locking-Some columns within a given row in a given table are locked.
6. Row level locking- Lock applies to a row in a table.

Hope this is helpful!!

Thank You!!