■Shared and Exclusive Locks
排他锁：可以定义在行级，表级；只能由一个Session持有
共享锁：只能定义在表级；能由多个Session持有，防止其它会话对同一表加表级排他锁；
DDL操作需要对表加表级排他锁，所以可以防止DDL操作。
■DML and DDL Locks
・All DML statements require at least two locks:
an exclusive lock on each row affected,and a shared lock on the table containing the row.
The exclusive lock prevents another session from interfering with the row, and the shared lock prevents another session from changing the table definition with a DDL statement.
・To execute DDL commands requires an exclusive lock on the object concerned.
■Manual Locking
・lock table t1 in exclusive mode;
release the table lock by issuing a COMMIT or ROLLBACK
・SELECT...FOR UPDATE NOWAIT
SELECT...FOR UPDATE WAIT
The SELECT...FOR UPDATE command will select rows and lock them in exclusive mode.
■Lock Contention
・"Deadlocks" are not the DBA’s problem; they are caused by bad program design and are resolved automatically by the database itself.
Information regarding deadlocks is written out to the alert log, with full details in a trace file;
・SET TRANSACTION READ ONLY
This will guarantee (without imposing any locks) that the session does not see any DML on any tables, committed or not, until it
terminates the read-only transaction with a COMMIT or ROLLBACK.
■
■Resolving Lock Contention
・ALTER SYSTEM KILL SESSION 'id,serial#'
When a session is terminated forcibly, any locks it holds will be released as its activetransaction is rolled back.
阅读(381) | 评论(0) | 转发(0) |
