Saturday, April 08, 2006
Oracle - DML & Locks
- INSERT INTO tablename
- UPDATE tablename
- DELETE [FROM] tablename
- MERGE tablename
- COMMIT
- SAVEPOINT savepointname
- ROLLBACK [TO savepointname]
VALUES (,....,....)
Note : If column list is omitted then the value list must contain a value for each column in the table in the same order.
INSERT INTO tablename (column list....)
SELECT columns, ..., ...Note 1: Using Sub-query in place of VALUE clause.
FROM table2
WHERE condition;
Note 2: Match the table1 column list to the column list of table2.
INSERT INTO tablename
SELECT * FROM table1;Note 1: copy all rows into an existing table.
INSERT INTO tablename
(SELECT columns,...,...VALUES (values....);
FROM table2
WHERE clause [WITH CHECK OPTION]
)
Note 1: Using Sub-query in place of tablename.
Note 2: Any rules on the columns of the base table must be followed.
Note 3: WITH CHECK OPTION limits the scope of the SQL command to the data-set fitting the Subquery. If a subquery is used in place of tablename in INSERT/UPDATE/DELETE statements specifying WITH CHECK OPTION, no changes that would produce rows not included in the sub-query will be permitted.
UPDATE tablename
SET column= value[WHERE condition];
[ , ..=...]
Note 1: value : can be a value or a sub-query for the column.
Note 2: Specific rows are updated with WHERE clause. Without the WHERE condition all the rows are modified.
DELETE [FROM] tablename
[WHERE condition];Note 1: Specific rows are deleted with WHERE clause. Without the WHERE condition all the rows are deleted.
MERGE INTO tablename
USING (table/view/subquery)
ON (join condition)
WHEN MATCHED THEN
[ UPDATE SETWHEN NOT MATCHED THEN
col1=value, col2=value
]
[ INSERT [(column list)];
VALUES (values...,...,...)]
INTO : target table being updated or inserted upon
USING : source of data (table/view/subquery)
ON : condition of merge
TRANSACTION CONTROL
COMMITSAVEPOINT name
ROLLBACK [TO SAVEPOINT name]
Note : DDL/DCL statements automatically commits thereby implicitly committing current transaction.
Purpose:
- Ensure data integrity
- Group logically related operations.
- Preview data change before committing (other users still see the old unchanged data)
ROLLBACK : Ends current transaction by discarding all pending data changes. The old values are restored back from the undo segment.
SAVEPOINT : Since these are logical, there is no way to list the created savepoints.
SET AUTOCOMMIT [ON|OFF]: If SET then each DML statement is committed as soon as it is executed.
Status before commit:
- DML basically effects the database buffers
- The current user can review the results by querying the database
- Other users can't view the results of DML statements done by current user. The Oracle Server institutes read consistency to insure that each user sees data as it exits at last COMMIT.
- the effected rows are locked, other users can't change the data in these locked rows.
- Data change made permanent and all users can view the results.
- Locks are released & all savepoints erased.
- Oracle does a statement level rollback if that statement fails during execution (by implementing an implicit savepoint for each DML statement).
- If a single DML statement fails during execution of transaction, its effects is undone by a statement level rollback, but the changes made by the previous DML statements is the transaction are not discarded. They can be committed or rollbacked explicitly by the user.
- The purpose of read consistency is to ensure that the user sees the data at the last commit. before a DML operation started.
- Implementation : Oracle keeps a partial copy of database in a Undo Segment. When an INSERT/UPDATE/DELETE is carried out, the old data is copied in Undo Segments and then the changes are made in the database. All users except the one who made the changes, see the data as it existed before the change - they view the snapshot of data stored in rollback segment.
- Before change is committed, only the user who made the data modifications can view the database with the alterations, everyone else sees the snapshot in the undo segment.
- COMMIT: The changes made to database become visible to all users. The space occupied by old data in the undo segment is freed for reuse.
- ROLLBACK : The original, older version of the data in the undo segment is written back to the table. Now all users see the database as it existed before the transaction began.
LOCKS
- Implicit locking occurs for all statements except SELECT.
- A share lock is automatically obtained at the table level during DML operation.
- An exclusive lock is acquired automatically for each row modified by DML statement.
- DDL locks occur when a database object is modified.
- Locks hold until commit or rollback.
Advertisements
|
ARCHIVES
|
Recent Postings
|
