Oracle Transactions and Locks (RPM)

If you use a SET TRANSACTION statement, then it must is the first statement in your transaction. However, a transaction need not has a SET TRANSACTION statement.

Syntax

set_transaction::=

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10005.htmDatabase transaction summary 1. Description

A set of SQL, a logical unit of work, an overall change in execution, or a whole fallback.

2. Transaction-related concepts

1) Transaction Commit and rollback: commit/rollback

2) Start and end of transaction

Start transaction: Connect to Database, execute DML, DCL, DDL statement

End transaction: 1. Execute DDL (for example, create TABLE), DCL (for example, Grant), the system automatically executes a COMMIT statement

2. Executive Commit/rollback

3. Exit/Disconnect the database automatically executes a COMMIT statement

4. Unexpected process termination, transaction auto rollback

5. Transaction commit will generate a unique system change number (SCN) saved to the transaction table

3) Save Point (savepoint): You can set the savepoint anywhere in the transaction so that the rollback

4) Four properties of the transaction acid:

1. atomicity (atomicity): SQL statements in a transaction are inseparable, either done or not

2. Consistency (consistency): Refers to the transaction operation, the data in the database is consistent, the data meet the Business rules constraints (such as the transfer of the account amount and transfer), and the atomicity of the corresponding.

3. Isolation (Isolation): Multiple concurrent transactions can run independently and not interfere with each other, and other transactions do not see the changes before a transaction modification data is committed.

4. Durability (persistent): After the transaction commits, the data modification is permanent.

5) Deadlock: When two transactions wait for each other to release resources, a deadlock is formed, and the following sections analyze in detail

Oracle Transaction ISOLATION LEVEL

1. Issues that may exist when two transactions concurrently Access database data

1. Fantasy reading:

The transaction T1 reads a statement that specifies the where condition and returns the result set. At this point the transaction T2 inserts a new row of records and commits, exactly satisfying the T1 's where condition. Then the T1 uses the same criteria to query again, the result set can see T2 inserted records, this new record is fantasy.

2. Non-repeatable reads:

The transaction T1 reads a row of records, and the transaction T2 modifies the record that the T1 just read and commits, and then T1 the query again, which is called non-repeatable reading, unlike the record that was first read.

3. Dirty reads:

Transaction T1 updated a row of records, not yet committed the changes, the T2 read the updated data, and then T1 to perform a rollback operation, cancel just the changes, so T2 read the row is invalid, that is, dirty data.

2.oracle Transaction ISOLATION LEVEL

Level of isolation supported by Oracle: (Dirty read not supported)

Read committed--not allow dirty read, allow fantasy read and non-repeatable read serializable--above three kinds are not allowed

The SQL standard also supports READ UNCOMMITTED (three types are allowed) and repeatable read (does not allow non-repeatable reads and dirty reads, allowing only fantasy reads)

The above differences are set up in the following sections, explained in isolation level analysis

Transaction-related statements

1. Summary of transaction-related statements

Set TRANSACTION----Setting transaction properties set CONSTRAINT-----Setting constraints SavePoint------------establishing a storage point release savepoint-- Release storage point rollback---------------rollback commit------------------Commit

2. Establishing a transaction, isolation level analysis

1) Establishing a transaction

The set TRANSACTION read only--transaction cannot have any action statements that modify data in the database, including INSERT, UPDATE, DELETE, create statement set TRANSACTION READ write--default settings, This option means that you can have access statements in a transaction, modify statements set TRANSACTION Isolation level READ Committedset TRANSACTION Isolation Level Serializable--serialzable can perform DML operations

Note: These statements are mutually exclusive and cannot be set to two or more than two options at a time

2) Read Only

eg

Set transaction Read only;

SELECT * from student;

Results:

Perform:

Update student set name= ' Little Meatball ' where id= ' CCC ';

Results:

Error starting command execution on line 3: Update student set name= ' where id= ' CCC ' ERROR report: SQL error: ORA-01456: cannot perform insert/delete/update operation in READ only transaction 01456. 00000-  "may not perform insert/delete/update operation inside a READ only transaction" *cause:    a non-ddl insert/de Lete/update or select for update operation           is attempted*action:   commit (or rollback) transaction, and Re-execute

3) Read Write

eg

Set TRANSACTION Read Write;select * from student;

Update student set Name= ' small meatball ' where id= ' CCC '; select * from student;

Results:

Transaction READ succeeded. 1 rows have been updated.

Conclusion: Permission to read and write

4) Isolation LEVEL Read Committed (Phantom Read and repeat read)

1. Establish two transactions as follows:

Transaction 1:

Set TRANSACTION  Read Write;select * from student;

Transaction 2:

Set TRANSACTION ISOLATION LEVEL read Committed;select * from student;

　

2. Modify a row of data in transaction 1 and commit

Update student set score=88 where id= ' FFF '; commit;

3. The results of the query in transaction 2 are as follows:

SELECT * from student;

Results:

Conclusion: The isolation level for transaction 2 is isolation levels Read Committed, which supports non-repeatable reads

4. Insert a row of data in transaction 1 and submit

INSERT into student values (' III ', ' small Dreams ', ' 1 ', 3,4,10);

5. View in Transaction 2:

SELECT * from student;

Conclusion: Transaction 2 Isolation levels are isolation level read Committed, allowing fantasy read

5) Isolation level serializable

1. Establish two transactions as follows:

Transaction 1:

Set TRANSACTION  Read Write;select * from student;

Transaction 2:

Set TRANSACTION ISOLATION Level Serializable;select * from student;

　

2. Modify a row of data in transaction 1 and commit

Update student set score=88 where id= ' FFF '; commit;

3. The results of the query in transaction 2 are as follows:

SELECT * from student;

Results:

Conclusion: The isolation level of transaction 2 is isolation levels serializable, non-repeatable reads are not supported

4. Insert a row of data in transaction 1 and submit

INSERT into student values (' III ', ' small Dreams ', ' 1 ', 3,4,10);

5. View in Transaction 2:

SELECT * from student;

Conclusion: The isolation level of transaction 2 is isolation levels serializable, which does not support fantasy reading

Oracle lock mechanism 1. Summarized

1) Description

A lock is a mechanism by which multiple transactions concurrently access a database object, which can implement concurrency control

2) Categories of locks in Oracle

1.DDL Lock: Oracle Auto-Apply and release

2.DML Lock: The transaction is applied at the beginning, using commit the latter rollback is released,

3. Internal lock: Managed by Oracle to protect the internal database structure

3) Granularity of Oracle Lock

1. Row-level Lock (TX): Blocks DML operations on this line until commit or rollback

2. Table-level Lock (TM):

3. Database-level lock: Eg: locks the database into read-only mode, ALTER DATABASE open read only;

Eg: set the database to restricted mode (used when importing and exporting databases): Alter system enable restricted session;

2. Mode of lock

1) Summary

Description

1. The greater the number, the higher the level

2) Eg:

Lock table student in row share Mode;lock table student in row exclusive mode;  --For Row modification lock table student in share mode;   --block other DML operations lock table student in share row exclusive mode; --Block other transactions Operation lock table student in exclusive mode;  --independent access to use

3. Lock view

1) Summary

SELECT * from v$session;--View session and lock information select * FROM v$session_wait;--View waiting session information SELECT * from v$lock;--all locks in System select * from V$lo DML lock in cked_object;--system

2) Eg:

Set transaction read Write;insert into student values (' jjj ', ' Xiao Xin ', ' 1 ', 3,4,90); SELECT * from V$locked_object;

Analysis: 1. Locked_mode:3, because we are doing insert, and thus is row-level exclusive lock row exclusive mode

　　

Dead lock

1. Description 1. A deadlock occurs when two users want to hold each other's resources.　　That is, two users waiting for each other to release resources, Oracle identified as a deadlock, in this case, at the expense of one user at the expense of another user to continue execution, the sacrifice of the user's transaction will be rolled back. 2. lORA-00060 error and recorded in the log file AlertSID.log of the database.  At the same time, a trace file is generated under User_dump_dest, which describes the deadlock information in detail. 2. Deadlock generation conditions

1. Mutual exclusion (Mutex): A resource cannot be shared and can only be used by one process.
2. Hold and wait (request and keep): The process that has already received the resource can request a new resource again.
3. No pre-emption (inalienable): the resources already allocated cannot be forcibly deprived of them from the corresponding process.
4. Circular Wait (cyclic wait condition): Several processes in the system make up loops in which each process waits for resources that are being consumed by neighboring processes.

3. Deadlock Simulation

Student table is as follows

1. Open two processes (use Sqldeveloper simulation here) to build two transactions

Transaction 1:

Set transaction read write;

Transaction 2:

Set transaction read write;

　

2. T1 time transaction 1 and transaction 2 respectively execute the following statements

Transaction 1:

Update student set classno=8 where id= ' III ';

Transaction 2:

Update student set score=score+10 where id= ' jjj ';

The results are as follows:

1 row has been updated

1 row has been updated

3. T2 time transaction 1 and transaction 2 respectively execute the following statements

Transaction 1:

Update student set score=score+20 where id= ' jjj ';

Transaction 2:

Update student set classno=9 where id= ' III ';

The results are as follows:

Error Report: SQL error: ORA-00060: Deadlock 00060 was detected while waiting for the resource. 00000-  "deadlock detected while waiting for resource" *cause:    transactions deadlocked one another while waiting fo R resources.*action: Look at the trace file to see the   transactions and resources           involved. Retry if necessary.

Note: The Red Fork at the Scriptrunner, out of the waiting state

4. Commit Transaction 2, transaction 1 results are as follows:

Note: spents 38.048s, the time to wait before transaction 2commit

4. Resolving deadlock conflicts

1) Execute commit or rollback END transaction

2) Terminate session

Or the example of a deadlock in 3.

Executing while waiting for a resource, finding a blocking session

Select Sid,serial#,username from V$session where SID in (select Blocking_session from v$session);

Perform:

Alter system kill session ' 423,896 ';

Results:

Error executing command on line 10: Alter system kill session ' 423,896 ' ERROR report: SQL Error: ORA-00027: Unable to terminate current session 00027. 00000-  "cannot kill current session" *cause:    attempted to use ALTER SYSTEM kill session to kill the current           SE Ssion.*action:   None.

WORKAROUND: For another session, close the current session (If it's necessary to kill the present session, do and from another session).

The other thread executes the above statement:

Alter system kill session ' 423,896 ';

The results are as follows: (Session kill succeeded, deadlock relieved, transaction 1 updated successfully)

5. Transaction and Deadlock Prevention Summary 1.　　Avoid applications that do not run long transactions.　　2. Frequent submissions to avoid long-time locking of rows.　　3. Avoid locking the table with the lock command. 4. Perform DDL operations during off-peak periods to perform long-running queries or transactions during off-peak hours. Also note that it is necessary to monitor the deadlock in the system to investigate why these locks are being kept, frequency; When a deadlock occurs, it is resolved by rolling back the transaction rollback or terminating the session.

Http://www.cnblogs.com/wishyouhappy/p/3698152.html

Oracle Transactions and Locks (RPM)