What do you mean by Upgrading and Downgrading of a LOCK?

Database
RMM
N/A

What do you mean by Upgrading and Downgrading of a LOCK?

Hi,

What do you mean by Upgrading and Downgrading of a LOCK?

Regards,

Rishikesh
1 REPLY

Re: What do you mean by Upgrading and Downgrading of a LOCK?

There are primarily 4 types of lock severities ACCESS, READ, WRITE and EXCLUSIVE and u can apply these locks at different levels namely at Database level or Table or View or Row hash level. When you use a select statement it applies a READ lock. While this lock is on , you cannot use an update statement on that table , that is you cannot get a WRITE lock. Similarly when there is a WRITE lock you cannot get a READ lock so you cannot Select. In such a scenario if you desire you can apply a locking modifier and downgrade the READ lock to an ACCESS lock so that you can continue querying though there is a chance that you may have dirty reads. Similarly you can upgrade the lock and obtain a higher lock. It is the locking modifier which does this. for example

LOCK TABLE tablename FOR ACCESS
SELECT …
FROM tablename …;

Check out the SQL manual below for better learning.
SQL Reference: Statement and Transaction Processing