Oracle database is the most widely used in the current database field, and it is also a huge system, fully understanding it, playing it not only requires certain theoretical knowledge, but also needs to develop experience and engineering experience. I am an Oracle a love, the following is some of my own experience to Oracle lock, I hope to share with you. The Oracle lock is specifically divided into the following categories: 1. According to the user and system division, it can be divided into auto lock and display lock auto lock: When a database operation is performed, it is default, the system automatically obtains all the database operations. The necessary lock. Display lock: In some cases, you need to use the data displayed by the user to display the data to be used, so that the database operation is performed better, the display lock is set by the user to the database object. 2. Press the lock level to divide, can be divided into shared locks and row lock sharing locks: shared lock allows a transaction to share access to a specific database resource - another transaction can also access or get the same shared lock. The shared lock provides high concurrency for the transaction, but if the poor transaction design shared lock is easy to cause deadlocks or data updates. Row it lock: After the transaction is set to the lock, the transaction is obtained separately, and another transaction cannot obtain the same object's shared lock or row lock before this transaction. 3. According to the operation, it can be divided into DML lock, DDL lock DML lock can also be divided into, line lock, table lock, dead lock - line lock: When transaction executing database insertion, update, delete operation, this transaction is automatically obtained Operating the rows of the operation line lock. - Table-level lock: This transaction will automatically obtain the table lock (shared lock) of the row after the transaction is latched to prevent the DDL statement from affecting the update of the record line. The transaction can also get a shared lock or row lock during the process, and only when the transaction displays a locking lock, the transaction will get the lock on the table, or use the lock table display. Define a shared lock (Lock Table specific usage, please refer to the relevant document). - Dead lock: When two affairs need a group of conflict locks, there is a deadlock. If the transaction 1 has a row of locks in the table A line record # 3, and wait for the transaction 2 to record the release of # 4 in the table A, and the transaction 2 has a row of lock in the table A record line # 4. And wait for a transaction; 1 Record the release of # 3 in Table A, the transaction 1 waits for each other with the transaction 2, so it causes a deadlock. Dead lock is generally due to poor business design. The deadlock can only use SQL: ALTER System Kill Session "SID, Serial #"; or uses a command to use the related operating system Kill process, such as UNIX Kill -9 SID, or use other tools to kill the deadlock process. DDL lock can also be divided into: DDL lock, shared DDL lock, analyze lock - row it DDL lock: create, modify, delete a DDL statement of a database object to get the lock lock. If you use the ALTER TABLE statement, in order to maintain the completion, consistency, legality of the data, this transaction gets a row of DDL locks. - Shared DDL lock: DDL statements that need to establish interdependent relationships between database objects usually need to share DDL locks. If you create a package, the process and function in this package references different database tables. When compiling this package, the transaction gets a shared DDL lock of the reference table. - Analysis Lock: Oracle uses a shared pool to store analysis and optimized SQL statements and PL / SQL programs, so that the application speed running the same statement is faster. A cached object in a shared pool gets the analysis lock of the database object it references. Analysis Lock is a unique DDL lock type, Oracle uses it tracks the dependencies between shared pool objects and the database object it references.
