In order to explore lock, I set up a test table test, the structure and test data are as follows:
sql & gt; select * from test;
ID NAME
———- ———-
6 yoxi
2 robinson
2 tttt
3 david
4 jason
5 lucy
1 test
7 rows selected.
Row share if row share locking is performed on a table, other sessions cannot perform exclusive locking on the table, but they can perform any other locking. That is to say, if a session performs row share lock on the table, other sessions can be executed except drop command.
In session 1:
sql & gt; lock table test in row share mode;
the table is locked.
Session 2:
sql & gt; lock table test in row share mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in row exclusive mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in share mode;
the table is locked.
SQL & gt; commit;
submit complete
sql & gt; lock table test in exclusive mode nowait;
lock table test in exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but the specified nowait method is used to obtain the resource
sql & gt; update test set id = 10;
7 lines have been updated.
SQL & gt; rollback;
rollback completed.
SQL & gt; drop table test;
drop table test
*
error in line 1:
ora-00054: the resource is busy, but it is specified to obtain the resource by nowait// in fact, the drop table command will perform exclusive locking on the table
row exclusive when executing insert, update, Delete will obtain a row exclusive lock, which has all the characteristics of row share. If a session obtains a row exclusive lock, then other sessions cannot perform exclusive lock on the table or share lock on the table. DML operation can be performed on the table, but drop is not allowed;
In session 1, the
sql & gt; lock table test in row exclusive mode;
table is locked.
Session 2:
sql & gt; lock table test in row share mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in row exclusive mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in share mode nowait;
lock table test in share mode nowait
*
error in line 1:
ora-00054: the resource is busy, but it is specified to get the resource in nowait mode
sql & gt; lock table test in exclusive mode nowait;
Lock table test in exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but the nowait mode is specified to obtain the resource
Share (shared lock) when a session has a share lock on a table, other sessions cannot lock the table in exclusive, row exclusive mode. In other words, if row exclusive is not available, insert, update, delete and drop operations cannot be performed.
In session 1, the
sql & gt; lock table test in share mode;
table is locked.
In session 2, the
sql & gt; lock table test in row share mode;
table is locked.
SQL & gt; lock table test in row exclusive mode nowait;
error in line 1:
ora-00054: the resource is busy, but the specified nowait method is used to get the resource
sql & gt; lock table test in share mode;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in exclusive mode nowait;
lock table test in exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but it is specified to get the resource in nowait mode
Exclusive (exclusive lock) when the drop command is executed, an exclusive lock is generated. As the name suggests, as long as a session has an exclusive lock on a table, other sessions cannot lock the table.
In session 1, the
sql & gt; lock table test in exclusive mode;
table is locked.
In session 2,
sql & gt; lock table test in row share mode nowait;
lock table test in row share mode nowait
*
there is an error in line 1:
ora-00054: the resource is busy, but the specified nowait method is used to obtain the resource
other locks do not need to be tested, because row share cannot obtain other locks, let alone other locks
Share row exclusive: if a session obtains a share row exclusive lock, other sessions can only obtain the row share lock on the table, that is to say, other sessions can’t perform insert, update, delete or drop operations on the table. Row share locking is good for nothing except preventing drop…
sql & gt; lock table test in share in session 1 Row exclusive mode;
the table is locked.
In session 2
sql & gt; lock table test in row share mode nowait;
the table is locked.
SQL & gt; commit;
commit completed.
SQL & gt; lock table test in row exclusive mode nowait;
lock table test in row exclusive mode nowait
*
error in line 1:
ora-00054: the resource is busy, but it is specified to get the resource in nowait mode
sql & gt; lock table test in share mode nowait;
Error in lock table test in share mode nowait
*
line 1:
ora-00054: the resource is busy, but the specified nowait method is used to obtain the resource
sql & gt; lock table test in exclusive mode nowait;
error in lock table test in exclusive mode nowait
*
line 1:
ora-00054: the resource is busy, However, it is specified to obtain resources in nowait mode
To sum up:
1. If a table is locked by a session, no matter what the lock is, the other session will not be able to obtain the exclusive lock for the table, that is, the table cannot be deleted. In fact, it is very easy to be the same. If someone is using that table, you must delete it
2. If row exclusive locking can be performed on a table, DML operation can certainly be performed
3. Share lock can be understood in this way. As the name suggests, share lock is used for sharing. Since it is shared, other users can only view it and cannot change it.
4. We can understand exclusive lock in this way. As the name suggests, exclusive lock is exclusive. Other users can only view it and cannot modify it.
Note: the lock command above can only get TM (table) lock
There are two kinds of locks generated by update, delete and insert. One is TM lock, the other is TX lock. TX lock means transaction lock
Select….. For update will also get a TX lock and a row exclusive lock
as well
Reprint address: http://blog.csdn.net/robinson1988/article/details/4675371