Download - 06 ICT118 SQL Txn Control Sem 2 2010
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
1/12
1
Database and InformationDatabase and InformationDatabase and InformationDatabase and Information
RetrievalRetrievalRetrievalRetrieval
ICT118
Lecture 6 Adding data to tables TransactionControl syntax
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.2
Oracle 11g: SQL
Chapter 5
Data Manipulation and Transaction ControlSlides from Cengage website for Casteel, Oracle 11g:SQL
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.3
ObjectivesObjectivesObjectivesObjectives
Use the INSERT command to add a record to anUse the INSERT command to add a record to anUse the INSERT command to add a record to anUse the INSERT command to add a record to anexisting tableexisting tableexisting tableexisting table
Manage virtual columns in data manipulationsManage virtual columns in data manipulationsManage virtual columns in data manipulationsManage virtual columns in data manipulations
Use quotes in data valuesUse quotes in data valuesUse quotes in data valuesUse quotes in data values
Use a subquery to copy records from an existingUse a subquery to copy records from an existingUse a subquery to copy records from an existingUse a subquery to copy records from an existingtabletabletabletable
Use the UPDATE command to modify the existingUse the UPDATE command to modify the existingUse the UPDATE command to modify the existingUse the UPDATE command to modify the existingrows of a tablerows of a tablerows of a tablerows of a table
Use substitution variables with an UPDATEUse substitution variables with an UPDATEUse substitution variables with an UPDATEUse substitution variables with an UPDATEcommandcommandcommandcommand
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
2/12
2
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.4
Objectives (continued)Objectives (continued)Objectives (continued)Objectives (continued)
Delete recordsDelete recordsDelete recordsDelete records
Manage transactions with transaction controlManage transactions with transaction controlManage transactions with transaction controlManage transactions with transaction controlcommands COMMIT, ROLLBACK, andcommands COMMIT, ROLLBACK, andcommands COMMIT, ROLLBACK, andcommands COMMIT, ROLLBACK, andSAVEPOINTSAVEPOINTSAVEPOINTSAVEPOINT
Differentiate between a shared lock and anDifferentiate between a shared lock and anDifferentiate between a shared lock and anDifferentiate between a shared lock and anexclusive lockexclusive lockexclusive lockexclusive lock
Use the SELECTFOR UPDATE command toUse the SELECTFOR UPDATE command toUse the SELECTFOR UPDATE command toUse the SELECTFOR UPDATE command tocreate a shared lockcreate a shared lockcreate a shared lockcreate a shared lock
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.5
INSERT CommandINSERT CommandINSERT CommandINSERT Command
Used to add rows to existing tablesUsed to add rows to existing tablesUsed to add rows to existing tablesUsed to add rows to existing tables
Identify the table in the INSERT INTO clauseIdentify the table in the INSERT INTO clauseIdentify the table in the INSERT INTO clauseIdentify the table in the INSERT INTO clause
Specify data in the VALUES clauseSpecify data in the VALUES clauseSpecify data in the VALUES clauseSpecify data in the VALUES clause
Can only add one row at a time to a tableCan only add one row at a time to a tableCan only add one row at a time to a tableCan only add one row at a time to a table
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.6
INSERT Command SyntaxINSERT Command SyntaxINSERT Command SyntaxINSERT Command Syntax
Enclose nonEnclose nonEnclose nonEnclose non----numeric data in singlenumeric data in singlenumeric data in singlenumeric data in single
quotesquotesquotesquotes
If a column list is not provided, a valueIf a column list is not provided, a valueIf a column list is not provided, a valueIf a column list is not provided, a value
must be assigned to each column in themust be assigned to each column in themust be assigned to each column in themust be assigned to each column in the
tabletabletabletable
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
3/12
3
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.7
INSERT Command ExamplesINSERT Command ExamplesINSERT Command ExamplesINSERT Command ExamplesNo Column List
Column List
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.8
Inserting NULL ValueInserting NULL ValueInserting NULL ValueInserting NULL Value
Omit column name from INSERT INTOOmit column name from INSERT INTOOmit column name from INSERT INTOOmit column name from INSERT INTO
clause column listclause column listclause column listclause column list
Substitute two single quotation marks orSubstitute two single quotation marks orSubstitute two single quotation marks orSubstitute two single quotation marks or
Use NULL keywordUse NULL keywordUse NULL keywordUse NULL keyword
NULL value input
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.9
Manage Virtual Column InputManage Virtual Column InputManage Virtual Column InputManage Virtual Column Input
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
4/12
4
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.10
Constraint ViolationsConstraint ViolationsConstraint ViolationsConstraint Violations
When you add or modify table data, the data isWhen you add or modify table data, the data isWhen you add or modify table data, the data isWhen you add or modify table data, the data is
checked for compliance with any applicablechecked for compliance with any applicablechecked for compliance with any applicablechecked for compliance with any applicable
constraintsconstraintsconstraintsconstraints
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.11
Activating the DEFAULT optionActivating the DEFAULT optionActivating the DEFAULT optionActivating the DEFAULT option
Include a column list in the INSERT statementInclude a column list in the INSERT statementInclude a column list in the INSERT statementInclude a column list in the INSERT statement
ignoring the column to use the DEFAULTignoring the column to use the DEFAULTignoring the column to use the DEFAULTignoring the column to use the DEFAULT
optionoptionoptionoption
Use the DEFAULT keyword as the value for theUse the DEFAULT keyword as the value for theUse the DEFAULT keyword as the value for theUse the DEFAULT keyword as the value for the
columncolumncolumncolumn
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.12
Inserting Data from an ExistingInserting Data from an ExistingInserting Data from an ExistingInserting Data from an Existing
TableTableTableTable
Substitute subquery for VALUES clauseSubstitute subquery for VALUES clauseSubstitute subquery for VALUES clauseSubstitute subquery for VALUES clause
Subquery
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
5/12
5
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.13
Modifying Existing RowsModifying Existing RowsModifying Existing RowsModifying Existing Rows
Modify rows using UPDATE commandModify rows using UPDATE commandModify rows using UPDATE commandModify rows using UPDATE command
Use UPDATE command to:Use UPDATE command to:Use UPDATE command to:Use UPDATE command to:
Add values to an existing row (replace NULL
values)
Change existing values
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.14
UPDATE CommandUPDATE CommandUPDATE CommandUPDATE Command
UPDATE clause identifies tableUPDATE clause identifies tableUPDATE clause identifies tableUPDATE clause identifies table
SET clause identifies column(s) being changedSET clause identifies column(s) being changedSET clause identifies column(s) being changedSET clause identifies column(s) being changed
and new value(s)and new value(s)and new value(s)and new value(s)
Optional WHERE clause specifies row(s) to beOptional WHERE clause specifies row(s) to beOptional WHERE clause specifies row(s) to beOptional WHERE clause specifies row(s) to be
changedchangedchangedchanged if omitted, all rows will beif omitted, all rows will beif omitted, all rows will beif omitted, all rows will be
updated!updated!updated!updated!
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.15
UPDATE Command SyntaxUPDATE Command SyntaxUPDATE Command SyntaxUPDATE Command Syntax
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
6/12
6
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.16
UPDATE Command ExampleUPDATE Command ExampleUPDATE Command ExampleUPDATE Command Example
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.17
Substitution VariablesSubstitution VariablesSubstitution VariablesSubstitution Variables
Prompts user for valuePrompts user for valuePrompts user for valuePrompts user for value
Identified by ampersand (&) preceding aIdentified by ampersand (&) preceding aIdentified by ampersand (&) preceding aIdentified by ampersand (&) preceding a
variable namevariable namevariable namevariable name
Can be used to create interactive scriptsCan be used to create interactive scriptsCan be used to create interactive scriptsCan be used to create interactive scripts
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.18
Substitution Variable ExampleSubstitution Variable ExampleSubstitution Variable ExampleSubstitution Variable Example
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
7/12
7
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.19
Deleting RowsDeleting RowsDeleting RowsDeleting Rows
DELETE command removes a row from aDELETE command removes a row from aDELETE command removes a row from aDELETE command removes a row from atabletabletabletable
WHERE clause
determines which
row(s) are removed
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.20
DELETE CommandDELETE CommandDELETE CommandDELETE Command OmittingOmittingOmittingOmitting
WHERE ClauseWHERE ClauseWHERE ClauseWHERE Clause
Omitting the WHERE clause removesOmitting the WHERE clause removesOmitting the WHERE clause removesOmitting the WHERE clause removes allallallall rowsrowsrowsrows
Example below removes all rows from theExample below removes all rows from theExample below removes all rows from theExample below removes all rows from theacctmanager tableacctmanager tableacctmanager tableacctmanager table
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.21
Transaction Control StatementsTransaction Control StatementsTransaction Control StatementsTransaction Control Statements
Results of data manipulation language (DML)Results of data manipulation language (DML)Results of data manipulation language (DML)Results of data manipulation language (DML)
are not permanently updated to a table untilare not permanently updated to a table untilare not permanently updated to a table untilare not permanently updated to a table until
explicit or implicit COMMIT occursexplicit or implicit COMMIT occursexplicit or implicit COMMIT occursexplicit or implicit COMMIT occurs
Transaction control statements can:Transaction control statements can:Transaction control statements can:Transaction control statements can:
Commit data through COMMIT command
Undo data changes through ROLLBACK command
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
8/12
8
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.22
COMMIT CommandCOMMIT CommandCOMMIT CommandCOMMIT Command
Explicit COMMIT occurs by executing COMMIT;Explicit COMMIT occurs by executing COMMIT;Explicit COMMIT occurs by executing COMMIT;Explicit COMMIT occurs by executing COMMIT;
Implicit COMMIT occurs when DDL commandImplicit COMMIT occurs when DDL commandImplicit COMMIT occurs when DDL commandImplicit COMMIT occurs when DDL command
is executed or user properly exits systemis executed or user properly exits systemis executed or user properly exits systemis executed or user properly exits system
Permanently updates table(s) and allows otherPermanently updates table(s) and allows otherPermanently updates table(s) and allows otherPermanently updates table(s) and allows other
users to view changesusers to view changesusers to view changesusers to view changes
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.23
ROLLBACK CommandROLLBACK CommandROLLBACK CommandROLLBACK Command
Used to undo changes that have not beenUsed to undo changes that have not beenUsed to undo changes that have not beenUsed to undo changes that have not beencommittedcommittedcommittedcommitted
Occurs when:Occurs when:Occurs when:Occurs when:ROLLBACK; is executed
System restarts after a crash
SAVEPOINT marks a specific spot withinSAVEPOINT marks a specific spot withinSAVEPOINT marks a specific spot withinSAVEPOINT marks a specific spot withinthe transactionthe transactionthe transactionthe transaction
Can ROLLBACK to a SAVEPOINT to undoCan ROLLBACK to a SAVEPOINT to undoCan ROLLBACK to a SAVEPOINT to undoCan ROLLBACK to a SAVEPOINT to undopart of the transactionpart of the transactionpart of the transactionpart of the transaction
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.24
Transaction Control ExampleTransaction Control ExampleTransaction Control ExampleTransaction Control Example
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
9/12
9
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.25
Transaction Control ExampleTransaction Control ExampleTransaction Control ExampleTransaction Control Example
(continued)(continued)(continued)(continued)
Only undo DML
actions after
SAVEPOINT
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.26
Table LocksTable LocksTable LocksTable Locks
Prevent users from changing same data orPrevent users from changing same data orPrevent users from changing same data orPrevent users from changing same data or
objectsobjectsobjectsobjects
Two typesTwo typesTwo typesTwo types
Shared prevents DML operations on a portion of
table
Exclusive locks table preventing other exclusiveor shared locks
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.27
LOCK TABLE CommandLOCK TABLE CommandLOCK TABLE CommandLOCK TABLE Command
Shared LockShared LockShared LockShared Lock
Locks portion of table affected by DMLLocks portion of table affected by DMLLocks portion of table affected by DMLLocks portion of table affected by DMLoperationoperationoperationoperation
Implicitly occurs during UPDATE or DELETEImplicitly occurs during UPDATE or DELETEImplicitly occurs during UPDATE or DELETEImplicitly occurs during UPDATE or DELETEoperationsoperationsoperationsoperations
Explicitly occurs through LOCK TABLEExplicitly occurs through LOCK TABLEExplicitly occurs through LOCK TABLEExplicitly occurs through LOCK TABLEcommand with SHARE MODE optioncommand with SHARE MODE optioncommand with SHARE MODE optioncommand with SHARE MODE option
Released when COMMIT (implicit or explicit) orReleased when COMMIT (implicit or explicit) orReleased when COMMIT (implicit or explicit) orReleased when COMMIT (implicit or explicit) orROLLBACK occursROLLBACK occursROLLBACK occursROLLBACK occurs
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
10/12
10
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.28
LOCK TABLE CommandLOCK TABLE CommandLOCK TABLE CommandLOCK TABLE Command
Exclusive LockExclusive LockExclusive LockExclusive LockImplicitly locks table for DDL operationsImplicitly locks table for DDL operationsImplicitly locks table for DDL operationsImplicitly locks table for DDL operations
CREATE or ALTER TABLECREATE or ALTER TABLECREATE or ALTER TABLECREATE or ALTER TABLE
Explicitly locked through LOCK TABLEExplicitly locked through LOCK TABLEExplicitly locked through LOCK TABLEExplicitly locked through LOCK TABLE
command with EXCLUSIVE MODE optioncommand with EXCLUSIVE MODE optioncommand with EXCLUSIVE MODE optioncommand with EXCLUSIVE MODE option
Released after execution of DDL operation orReleased after execution of DDL operation orReleased after execution of DDL operation orReleased after execution of DDL operation or
after user exits systemafter user exits systemafter user exits systemafter user exits system
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.29
SELECTFOR UPDATESELECTFOR UPDATESELECTFOR UPDATESELECTFOR UPDATE
CommandCommandCommandCommand
Creates shared lock on retrieved portion ofCreates shared lock on retrieved portion ofCreates shared lock on retrieved portion ofCreates shared lock on retrieved portion of
tabletabletabletable
Prevents one user from changing a row whilePrevents one user from changing a row whilePrevents one user from changing a row whilePrevents one user from changing a row while
another user is selecting rows to be changedanother user is selecting rows to be changedanother user is selecting rows to be changedanother user is selecting rows to be changed
Released through implicit or explicit commitReleased through implicit or explicit commitReleased through implicit or explicit commitReleased through implicit or explicit commit
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.30
SELECTFOR UPDATESELECTFOR UPDATESELECTFOR UPDATESELECTFOR UPDATECommand SyntaxCommand SyntaxCommand SyntaxCommand Syntax
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
11/12
11
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.31
SummarySummarySummarySummary
Data manipulation language (DML) includes the INSERT,Data manipulation language (DML) includes the INSERT,Data manipulation language (DML) includes the INSERT,Data manipulation language (DML) includes the INSERT,
UPDATE, DELETE, COMMIT, and ROLLBACKUPDATE, DELETE, COMMIT, and ROLLBACKUPDATE, DELETE, COMMIT, and ROLLBACKUPDATE, DELETE, COMMIT, and ROLLBACKcommandscommandscommandscommands
The INSERT INTO command is used to add new rows to anThe INSERT INTO command is used to add new rows to anThe INSERT INTO command is used to add new rows to anThe INSERT INTO command is used to add new rows to an
existing tableexisting tableexisting tableexisting table
The column list specified in the INSERT INTO clause mustThe column list specified in the INSERT INTO clause mustThe column list specified in the INSERT INTO clause mustThe column list specified in the INSERT INTO clause must
match the order of data entered in the VALUES clausematch the order of data entered in the VALUES clausematch the order of data entered in the VALUES clausematch the order of data entered in the VALUES clause
A virtual column must be ignored in all DML actionsA virtual column must be ignored in all DML actionsA virtual column must be ignored in all DML actionsA virtual column must be ignored in all DML actions
because the database system generates this columnbecause the database system generates this columnbecause the database system generates this columnbecause the database system generates this column
value automaticallyvalue automaticallyvalue automaticallyvalue automatically
You can use a NULL value in an INSERT INTO commandYou can use a NULL value in an INSERT INTO commandYou can use a NULL value in an INSERT INTO commandYou can use a NULL value in an INSERT INTO command
by including the keyword NULL, omitting the columnby including the keyword NULL, omitting the columnby including the keyword NULL, omitting the columnby including the keyword NULL, omitting the column
from the column list of the INSERT INTO clause, orfrom the column list of the INSERT INTO clause, orfrom the column list of the INSERT INTO clause, orfrom the column list of the INSERT INTO clause, or
entering two single quotes (without a space) in theentering two single quotes (without a space) in theentering two single quotes (without a space) in theentering two single quotes (without a space) in the
position of the NULL valueposition of the NULL valueposition of the NULL valueposition of the NULL value
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.32
Summary (continued)Summary (continued)Summary (continued)Summary (continued)
To assign a DEFAULT option value, a column must beTo assign a DEFAULT option value, a column must beTo assign a DEFAULT option value, a column must beTo assign a DEFAULT option value, a column must be
excluded from the column list in an INSERT statement orexcluded from the column list in an INSERT statement orexcluded from the column list in an INSERT statement orexcluded from the column list in an INSERT statement or
the keyword DEFAULT must be included as the value for thethe keyword DEFAULT must be included as the value for thethe keyword DEFAULT must be included as the value for thethe keyword DEFAULT must be included as the value for the
columncolumncolumncolumn
In a DML statement, two single quotes together must be usedIn a DML statement, two single quotes together must be usedIn a DML statement, two single quotes together must be usedIn a DML statement, two single quotes together must be used
to represent a single quote in a valueto represent a single quote in a valueto represent a single quote in a valueto represent a single quote in a value
If rows are copied from a table and entered in an existing tableIf rows are copied from a table and entered in an existing tableIf rows are copied from a table and entered in an existing tableIf rows are copied from a table and entered in an existing table
by using a subquery in the INSERT INTO command, theby using a subquery in the INSERT INTO command, theby using a subquery in the INSERT INTO command, theby using a subquery in the INSERT INTO command, the
VALUES clause must be omitted because its irrelevantVALUES clause must be omitted because its irrelevantVALUES clause must be omitted because its irrelevantVALUES clause must be omitted because its irrelevantYou can change the contents of a row or group of rows withYou can change the contents of a row or group of rows withYou can change the contents of a row or group of rows withYou can change the contents of a row or group of rows with
the UPDATE commandthe UPDATE commandthe UPDATE commandthe UPDATE command
You can use substitution variables to allow you to execute theYou can use substitution variables to allow you to execute theYou can use substitution variables to allow you to execute theYou can use substitution variables to allow you to execute the
same command several times with different data valuessame command several times with different data valuessame command several times with different data valuessame command several times with different data values
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.33
Summary (continued)Summary (continued)Summary (continued)Summary (continued)DML operations arent stored permanently in a table until aDML operations arent stored permanently in a table until aDML operations arent stored permanently in a table until aDML operations arent stored permanently in a table until a
COMMIT command is issued implicitly or explicitlyCOMMIT command is issued implicitly or explicitlyCOMMIT command is issued implicitly or explicitlyCOMMIT command is issued implicitly or explicitly
A transaction consists of a set of DML operationsA transaction consists of a set of DML operationsA transaction consists of a set of DML operationsA transaction consists of a set of DML operations
committed as a blockcommitted as a blockcommitted as a blockcommitted as a block
Uncommitted DML operations can be undone by issuing theUncommitted DML operations can be undone by issuing theUncommitted DML operations can be undone by issuing theUncommitted DML operations can be undone by issuing the
ROLLBACK commandROLLBACK commandROLLBACK commandROLLBACK command
A SAVEPOINT serves as a marker for a point in aA SAVEPOINT serves as a marker for a point in aA SAVEPOINT serves as a marker for a point in aA SAVEPOINT serves as a marker for a point in a
transaction and allows rolling back only a portion of thetransaction and allows rolling back only a portion of thetransaction and allows rolling back only a portion of thetransaction and allows rolling back only a portion of the
transactiontransactiontransactiontransaction
Use the DELETE command to remove records from a table;Use the DELETE command to remove records from a table;Use the DELETE command to remove records from a table;Use the DELETE command to remove records from a table;
if the WHERE clause is omitted, all rows in the table areif the WHERE clause is omitted, all rows in the table areif the WHERE clause is omitted, all rows in the table areif the WHERE clause is omitted, all rows in the table are
deleteddeleteddeleteddeleted
Table locks can be used to prevent users from mistakenlyTable locks can be used to prevent users from mistakenlyTable locks can be used to prevent users from mistakenlyTable locks can be used to prevent users from mistakenly
overwriting changes made by other usersoverwriting changes made by other usersoverwriting changes made by other usersoverwriting changes made by other users
-
8/2/2019 06 ICT118 SQL Txn Control Sem 2 2010
12/12
12
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 6.34
Summary (continued)Summary (continued)Summary (continued)Summary (continued)
Table locks can be in SHARE mode or EXCLUSIVE modeTable locks can be in SHARE mode or EXCLUSIVE modeTable locks can be in SHARE mode or EXCLUSIVE modeTable locks can be in SHARE mode or EXCLUSIVE mode
EXCLUSIVE mode is the most restrictive table lock andEXCLUSIVE mode is the most restrictive table lock andEXCLUSIVE mode is the most restrictive table lock andEXCLUSIVE mode is the most restrictive table lock andprevents any other user from placing any locks on theprevents any other user from placing any locks on theprevents any other user from placing any locks on theprevents any other user from placing any locks on the
same tablesame tablesame tablesame table
A lock is released when a transaction control statement isA lock is released when a transaction control statement isA lock is released when a transaction control statement isA lock is released when a transaction control statement is
issued, a DDL statement is executed, or the user exitsissued, a DDL statement is executed, or the user exitsissued, a DDL statement is executed, or the user exitsissued, a DDL statement is executed, or the user exits
the system by using the EXIT commandthe system by using the EXIT commandthe system by using the EXIT commandthe system by using the EXIT command
SHARE mode allows other users to place shared locks onSHARE mode allows other users to place shared locks onSHARE mode allows other users to place shared locks onSHARE mode allows other users to place shared locks on
other portions of the table, but it prevents users fromother portions of the table, but it prevents users fromother portions of the table, but it prevents users fromother portions of the table, but it prevents users from
placing an exclusive lock on the tableplacing an exclusive lock on the tableplacing an exclusive lock on the tableplacing an exclusive lock on the table
The SELECT . . . FOR UPDATE command can be used toThe SELECT . . . FOR UPDATE command can be used toThe SELECT . . . FOR UPDATE command can be used toThe SELECT . . . FOR UPDATE command can be used to
place a shared lock for a specific row or rows; the lockplace a shared lock for a specific row or rows; the lockplace a shared lock for a specific row or rows; the lockplace a shared lock for a specific row or rows; the lock
isnt released unless a DDL command is issued or theisnt released unless a DDL command is issued or theisnt released unless a DDL command is issued or theisnt released unless a DDL command is issued or the
user exits the systemuser exits the systemuser exits the systemuser exits the system