Latch and Lock:

In real world our door have latch which is a temporary lock where others can open the door without the key and permanent lock where one cannot open the door without the key other than something like breaking in.

The same concept is applied in our database.

Note: To update a specific row using where clause need both the update and select permission on the object. That the reason this error is showing when I am trying to update a record even though I have already given the update privilege to this user.

If I used only update statement without the where clause then it wouldn't cause the error. But as I used where clause I also needed the select privilege also.

See now this works.

Lock applied on a data block:

We know that when an user does a dml transaction which involves a changing an already existing data block like update, this is what follows:

I. The server process copies the data block into database buffer cache and a lock is applied on the original data block.

II. The value of the original data block is copied to undotds.dbf datafile and the address of that data file is put in place of the original data block. Therefore when another user do a select statement on that specific record the old record from the undotbs.dbf datafile is taken to the database buffer cache and is displayed. This is how read consistency is maintained.

III. Now this lock remains until the transaction is completely i,e the user do a commit or rollback or run a DDL (like alter) on the object (as DDL is auto-commit).

Now the question is when this lock is applied can another user do an update on the locked record.

I do an update on the employees table on the record of id 105 and didn't commit so it remain locked. Now I login as another user and tried to do another update on the same record id 105. See the cursor is stuck.

For the information the lock is applied on the record (row) level not at the object level. So I could run an object statement on a different row (id 102) and it got updated. But see when I tried to do an update statement on the locked row (id 105) the cursor is stuck.

Now this can happen. An user forgot to commit or rollback after doing a transaction. Its an human error. It can happen that the user that forgot to complete the transaction is of different time zone and many more. If this happens then another user tries to do an update on the same record their query will get stuck for a indefinite time. They will come to dba in that time.

Now DBA can check which user has locked the record through this 2 dynamic views v$lock and v$session.

Now when an user logins they generates a session which are identified by session id. We can see which user through which session is blocking the query.

select s.sid ||','||s.serial# as Blocked_session, s.username as Blocked_user, b.sid ||','||b.serial# as Blocking_session, b.username as Blocking_user, s.event, s.seconds_in_wait from v$session s, v$session b where s.blocking_session = b.sid;

Now after finding that out we can kill that session using this command.

Alter system kill session 'sid,serial_id' immeidiate;

This sid is the session id. The kill session needs both the sid and serial id in this format.

Example: alter system kill session '94,11342' immediate;

The previous situation:

After the killing session:

---

See the blocked user query got completed.

Important: But remember before doing this always inform the upper management and the business users about this situation. See if we kill that session that user's incomplete transaction will be deleted. We don't know what is the sensitivity of the transaction that user did and what is the repercussions of it being deleted. Like it can be crucial business data which the original user has updated and if we delete that then it will be our responsibility. So always, always inform the higher management of the situation before doing this and get their written approval (written officially maybe in email, not verbal). Only after getting the approval the run the command.

Understanding how the lock and the session behaves:

1. Multiple blocking sessions of the same user:

This is a situation I have created.

Here hr user have blocked 3 different users.

The HR user is locking SYS, NAME AND AG Users, but its blocking SYS and AG user in a single session though their sql are different as shown in the pic. So it means the serial id doesn't depend on the sql. And its blocking NAME user in a different session.

---

You can see that HR user has blocked the rows with 102 and 103 in a single session. It has updated both 102 and 103 row but not commited them which has created the lock for the SYS and AG user.

Here it has blocked the row 104 which has blocked the query of NAME user.

---

Lets find out what will happen if we kill the session of the HR user which is blocking both SYS and AG users session '78,23499'.

---

It has cleared the queries of both SYS and AG users but the NAME user's query is still blocked. It has proved that session killing is not user specific as all the blocking sessions are of HR user but killing one session has only killed that specific session and didn't touch the other session of that user.

---

---

Here after clearing the other session the NAME user's queries are cleared. And killing the session has deleted the actions of the uncommitted dml queries of the HR user associated with the killed session. See none of the updates made by the HR User has been made in the table.

2. If the user disconnects a session before commiting or rollbacking:

This is the previous situation of the table

---

I do an update to the row 105.

But didn't commit before ending the session. But still the last name of the record 105 replaced from 'Austin' to ' '.

I again don't commit before ending the session. So why the row stayed updated?