Introduction to NonStop SQL/MP

Modifying Data
How to Use NonStop SQL
2–8 113425 Tandem Computers Incorporated
A shorthand view can refer to a protection view just as it refers to a table. That means
you can simplify data access at the same time as you restrict data access.
Table 2-1 compares protection views and shorthand views.
Table 2-1. Comparison of Protection Views and Shorthand Views
Protection View Shorthand View
Derived from only one table Derived from any number of tables and other
views
Can be secured separately from the table on
which it is based
Derives its security from tables or views on which
it is based
Allows modification of data Allows reading but not modification of data
Modifying Data NonStop SQL/MP allows you to insert, delete, and update data as well as retrieve it.
For example, to add information about a new employee, you can use the INSERT
statement to add a row to the EMPLOYEE table. To give certain employees a five-
percent raise, you can issue the following UPDATE statement:
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.05
WHERE SALARY BETWEEN 20000.00 AND 45000.00 ;
Concurrent Updates and
Locking
In large business applications, particularly OLTP applications, hundreds or thousands
of users can modify the database at any time. Suppose, for example, that many
customers simultaneously withdraw cash from the hundreds of ATMs belonging to a
large bank. NonStop SQL/MP has the power to retrieve and modify the data for all
these transactions and quickly respond to each customer.
This flexibility, however, raises the issue of data integrity and consistency. Each user
must be able to finish modifying a particular value (for example, debiting a checking
account by a certain amount) without interference from other concurrent
modifications.
NonStop SQL/MP provides efficient locking mechanisms, including row-level and
range locking, to ensure that only one user at a time can update a particular row (or
rows). When a row is updated, NonStop SQL/MP automatically chooses an exclusive
lock, preventing any other users from having access to that row. To ensure the highest
performance, NonStop SQL/MP can lock a single row, a range of rows, a partition of a
table, or an entire table.
Normally, NonStop SQL/MP chooses the appropriate locking strategy, but in some
instances you might want to impose a specific strategy. NonStop SQL/MP gives you
some control over the extent of locking by providing the LOCK TABLE and
CONTROL TABLE statements. For more information about locking, refer to the
NonStop SQL/MP Query Guide and NonStop SQL/MP Reference Manual.