What is a deadlock in SQL Server?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
SQL Server detects the situation after four denials and refuses further shared locks.
A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
SQL Server detects the situation after four denials and refuses further shared locks.
A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
What are “GRANT” and “REVOKE’
statements in SQL?
GRANT statement grants rights
to the objects (table). While revoke does the vice-versa of it, it removes
rights from the object.
How do you find the Second
highest Salary?
Answer:
We can write a sub-query to achieve the result
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.