Successful development of Chapter 1 of Oracle Applications
Bitmap index is only applicable to low base (low-cardinality) value, but the frequently updated list does not apply.
(So-called low cardinality column refers to this column can be only a few values.)
Reasons: the use of bitmap index, a key point to multi-line, may be hundreds or even more, if you update a bitmap index key, then the key point to the hundreds of records will be updated with what you actually are effectively the same as with locked.
If PROCESSED_FLAG out only two values: Y and N, for the record inserted into the table, the column value is N (for untreated). Other processes to read and process the record, they will put the updated column value from N Y. If someone inserts a new record (PROCESSED_FLAG column is N). At this point, you want to read this table and record the process of dealing with N records can not be modified Y recorder. The reason is that in order to update this column from N to Y, to lock the bitmap with a key. In fact, want to insert a new record in this table the other sessions will be blocked, because they also want the bitmap index key lock. Simply put, developers realized that a group structure, which allows only one person up to insert or update!
Example: Use two sessions to demonstrate the occurrence of blocking
SQL> create table t (processed_flag varchar2 (2));
SQL> create bitmap index t_idx on t (processed_flag);
SQL> insert into t value ('N');
1 row created.
In another sql * plus session, execute the following command:
SQL> insert into t values ('N');
This statement will "hang" until the first session to issue a commit block location.
1.3 database application development right (and wrong) way
1.3.1 Understanding Oracle architecture
SQL Server there is a very common practice that you want to execute each concurrent statement opens a database connection. If you want to Run 5 queries, SQL Server may be seen in the five connections. If Windows is not for multi-threaded multi-process design of the same. In Oracle, whether you want to run 5 or 500 Query query, want to open up a connection. Oracle is the basis of this concept design. Therefore, SQL Server in the normal practice is not advocated in Oracle: you may not want to maintain multiple database connections.
1.3.2 Use bind variables
Oracle will be parsed, compiled SQL connectivity other content stored in the shared pool (shared pool), this is the system global area (System Global Area, SGA) is very important in a shared memory structure. This structure can complete the "smooth" operation, provided that the developer will use to bind the majority of cases. If you really want Oracle to run slowly, or even near-standstill, as long as you do not use bind variables can be done.
Bind variable (bind variable) is a placeholder in the query. For example:
select * from emp where empno = 123;
Or, you can also bind variables: empno is set to 123, and execute the following query:
select * from emp where empno =: empno;
If the amount used in the query directly (constant), then each query is a whole new area of inquiry, seems to have never seen in the database, the query must be parsed, qualified (2 analysis), security check, optimization . Briefly put, is that you perform each different statement compiled at execution time.
The second query uses a bind variable: empno, to provide a variable in the query execution. This query is only compiled once, then it will query plan stored in a shared pool (library cache) for later access and reuse the query plan.
1.3.2 understand the concurrency control
1 to achieve lock
Oracle only when the data plus the modified row-level locking.
(2) to prevent lost updates
select * from resources where resource_name =: room_name FOR UPDATE;
Before the deployment of resources to lock resources (in this room), in other words, the query in the resources table of the resource prior to the scheduled lock resources. By locking the resources to be scheduled, the developer can guarantee that others will not also modify the scheduling of resources. Others must wait until he presented things up. If someone else has locked this line, we will block and wait.
(1) into sql * plus (Session 1)
SQL> select * from TMP_DEST where DEST_SYS_ID = 'IC' FOR UPDATE;
DEST_SYS_ID SYS_NAME IP_ADDR PORT USR_NAME PASSWD SUBS_MODE TRAN_MODE PUBLISH_MODE UPD_FLAG
----------- ------------ ------------- ---- -------- - -------------- --------- --------------------------- ---------- ------------ --------
IC reporting platform 3 New Line 184.108.40.206 23 dcdsftp 56864857DC705A33 0 com.bocsoft.dcds.dataproces.FTPUpload 1 A
(2), the newly opened window into another sql * plus session (session 2):
SQL> update TMP_DEST set port = '24 'where DEST_SYS_ID =' IC ';
Blinking cursor after the execution, in the waiting (blocked) state.
(3), in the sql * plus session 1 to commit:
(4), 2 sql * plus session screen:
1 row updated.
Chapter 2 Architecture Overview
2.1 Definitions and examples of database
Database (database): the physical operating system files or disk (disk) of the collection.
Instance (instance): a set of Oracle background processes / threads and a shared memory area, which computer memory to run by the same thread / process sharing.
Example is a set of operating system processes (or a multi-threaded process) and some memory. These processes can operate the database; the database is a collection of files (including data files, temporary files, redo logs and control files). At any time, only one instance of a group of related files (associated with a database). Most cases, the opposite is true: only one instance of a database on its operation. However, Oracle Real Application Clusters (Real application clusters, RAC) is an exception, this is an option provided by Oracle, allows multiple cluster environment in computer operation, so that you can load multiple instances of the same time and open a database (in a shared physical disk).
Chapter 6 Lock
Update the table in Oracle will not block on this table check; row-level locks are no overhead.
6.2.2 Pessimistic Pessimistic locking (pessimistic locking) only for a state (stateful) or link (connected) environment, that is, applied to the database you have a continuous connection, but at least things in life which only you a person using this connection.
6.2.3 Optimistic locking Optimistic locking (optimistic locking), which are delayed until all the locks before the update was about to do. Namely, that data is not modified by other users. In this way, the update of the user "failure" will increase the likelihood. This user wants to update his data row that the data has been modified, it must start from scratch.
In the application while retaining the old and new values, and then update the data using the following update statement, which is a popular implementation of optimistic locking:
set column1 =: new_column1, column2 =: new_column2, ......
where primary_key =: primary_key
and column1 = ld_column1 and column2 = ld_column2
If you update the zero line, indicating that the update failed.
1 Use optimistic locking version of the column to protect the table for each increase of one. This column is NUMBER or generally DATA / TIMESTAMP columns, usually through a line of flip-flop on the table to maintain. Each time you modify, this trigger is responsible for increasing the value of NUMBER columns, or update the DATA / TIMESTAMP / column.
If the application to implement optimistic concurrency control, but just save the value of the additional columns, other columns need to save all the "before" image. Just update the application authentication request that moment, the value of this column in the database and read out the value of the first match. If the two values are equal, it means the line has not been updated.
create table dept
(Deptno number (2),
dname varchar2 (14),
loc varchar2 (13),
last_mod timestamp with time zone
constraint dept_pk primary key (deptno)
Can not always rely on each application to maintain this field. Suggestions into the stored procedure. Stored procedures can take more than updated to use bind variables as input, perform the same update. 0 rows updated is detected, the stored procedure returns an exception to the customers, let customers know that update failed.
Does not recommend the use of triggers, will introduce a lot of overhead, but such a simple task did not need to use them.
2 uses a checksum of the optimistic locking
3. ORA_ROWSCN optimistic locking using
6.2.5 If a session is holding a blocking lock resources, and another session at the request of the resource, there will be blocking (blocking). In this way, the requested session will be blocked, it will "hang", but to give up holding the session lock resources.
There are five common database DML statements may be blocked: INSERT, UPDATE, DELETE, MERGE, and SELECT FOR UPDATE. For a block of SELECT FOR UPDATE, simply increase the NOWAIT clause, it will not be blocked.
6.2.6 deadlock if there are two sessions, each session of the resources you want to hold another session, this time there will be deadlock (deadlock).
6.3 Lock Types
There are three types of Oracle in the lock:
DML locks: DML data manipulation language on behalf of (Data Manipulation Language). In general, said the SELECT, INSERT, UPDATE, MERGEE and DELETE statements. DML lock mechanism allows concurrent execution of data modifications. For example, DML locks may lock a particular row of data, or lock all rows in the table table-level locks.
DDL locks: DDL data definition language on behalf of (Data Definition Language), such as CREATE and ALTER statements, etc. DDL locks protect the definition of object structure.
Internal locks and latches: ...
Chapter 7 with multi-version concurrency
7.1 What is concurrency control concurrency control (concurrency control) is a database provides a collection of functions that allow multiple people to simultaneously access and modify data.
Oracle Management shared lock is concurrent access to database resources and to prevent concurrent database transactions between the "mutual interference" one of the core mechanisms
7.2 transaction isolation level
ANSI (American National Standards Institute American National Standards Institute) / ISO (International Standardization Organization ISO) SQL standard defines four isolation levels, for the same transaction, using different isolation levels have different results. In other words, even if the same input, but in the same way to accomplish the same work, it may be completely different answers, depending on transaction isolation level in the. These isolation levels are based on three "phenomena" to the definition, what is given isolation level that may allow or not allow the 3 phenomena:
Dirty read (dirty read): read uncommitted data is dirty read. As long as people are reading and writing to open a 0S file, you can achieve the effect of dirty read.
Non-repeatable reads (nonrepeatable read): If you have time to read a row in T1, the T2 re-read this line, this line may have been modified. Perhaps it has disappeared, may be updated, and so on.
Phantom read (phantom read): This shows that if you execute a query in the T1 time, but in the T2 time to execute the query, this time may have added another to the data line, which will affect your results. And the difference is non-repeatable read: In phantom read, has read the data will not change, but compared with the previous, more data will satisfy your query.
ANSI isolation level
Non-repeatable read isolation level dirty phantom read
READ UNCOMMITTED (read uncommitted) Allowed Allowed Allowed
READ COMMITTED (read committed) to allow allow
REPEATABLE READ (repeatable read) to allow