Sql | MacLochlainns Weblog

Data Manipulation Language (DML)

DML statements add data to, change data in, and remove data from tables. This section examines four DML statements—the INSERT, UPDATE, DELETE, and MERGE statements—and builds on concepts of data transactions. The INSERT statement adds new data, the UPDATE statement changes data, the DELETE statement removes data from the database, and the MERGE statement either adds new data or changes existing data.

Any INSERT, UPDATE, MERGE, or DELETE SQL statement that adds, updates, or deletes rows in a table locks rows in a table and hides the information until the change is committed or undone (that is, rolled back). This is the nature of ACID-compliant SQL statements. Locks prevent other sessions from making a change while a current session is working with the data. Locks also restrict other sessions from seeing any changes until they’re made permanent. The database keeps two copies of rows that are undergoing change. One copy of the rows with pending changes is visible to the current session, while the other displays committed changes only.

ACID Compliant Transactions

ACID compliance relies on a two-phase commit (2PC) protocol and ensures that the current session is the only one that can see new inserts, updated column values, and the absence of deleted rows. Other sessions run by the same or different users can’t see the changes until you commit them.

ACID Compliant INSERT Statements

The INSERT statement adds rows to existing tables and uses a 2PC protocol to implement ACID- compliant guarantees. The SQL INSERT statement is a DML statement that adds one or more rows to a table. Oracle supports a VALUES clause when adding a single-row, and support a subquery when adding one to many rows.

The figure below shows a flow chart depicting an INSERT statement. The process of adding one or more rows to a table occurs during the first phase of an INSERT statement. Adding the rows exhibits both atomic and consistent properties. Atomic means all or nothing: it adds one or more rows and succeeds, or it doesn’t add any rows and fails. Consistent means that the addition of rows is guaranteed whether the database engine adds them sequentially or concurrently in threads.

Concurrent behaviors happen when the database parallelizes DML statements. This is similar to the concept of threads as lightweight processes that work under the direction of a single process. The parallel actions of a single SQL statement delegate and manage work sent to separate threads. Oracle supports all ACID properties and implements threaded execution as parallel operations. All tables support parallelization.

After adding the rows to a table, the isolation property prevents any other session from seeing the new rows—that means another session started by the same user or by another user with access to the same table. The atomic, consistent, and isolation properties occur in the first phase of any INSERT statement. The durable property is exclusively part of the second phase of an INSERT statement, and rows become durable when the COMMIT statement ratifies the insertion of the new data.

ACID Compliant UPDATE Statements

An UPDATE statement changes column values in one-to-many rows. With a WHERE clause, you update only rows of interest, but if you forget the WHERE clause, an UPDATE statement would run against all rows in a table. Although you can update any column in a row, it’s generally bad practice to update a primary or foreign key column because you can break referential integrity. You should only update non-key data in tables—that is, the data that doesn’t make a row unique within a table.

Changes to column values are atomic when they work. For scalability reasons, the database implementation of updates to many rows is often concurrent, in threads through parallelization. This process can span multiple process threads and uses a transaction paradigm that coordinates changes across the threads. The entire UPDATE statement fails when any one thread fails.

Similar to the INSERT statement, UPDATE statement changes to column values are also hidden until they are made permanent with the application of the isolation property. The changes are hidden from other sessions, including sessions begun by the same database user.

It’s possible that another session might attempt to lock or change data in a modified but uncommitted row. When this happens, the second DML statement encounters a lock and goes into a wait state until the row becomes available for changes. If you neglected to set a timeout value for the wait state, such as this clause, the FOR UPDATE clause waits until the target rows are unlocked:

WAIT n

As the figure below shows, actual updates are first-phase commit elements. While an UPDATE statement changes data, it changes only the current session values until it is made permanent by a COMMIT statement. Like the INSERT statement, the atomic, consistent, and isolation properties of an UPDATE statement occur during the first phase of a 2PC process. Changes to column values are atomic when they work. Any column changes are hidden from other sessions until the UPDATE statement is made permanent by a COMMIT or ROLLBACK statement, which is an example of the isolation property.

Any changes to column values can be modified by an ON UPDATE trigger before a COMMIT statement. ON UPDATE triggers run inside the first phase of the 2PC process. A COMMIT or ROLLBACK statement ends the transaction scope of the UPDATE statement.

The Oracle database engine can dispatch changes to many threads when an UPDATE statement works against many rows. UPDATE statements are consistent when these changes work in a single thread-of-control or across multiple threads with the same results.

As with the INSERT statement, the atomic, consistent, and isolation properties occur during the first phase of any UPDATE statement, and the COMMIT statement is the sole activity of the second phase. Column value changes become durable only with the execution of a COMMIT statement.

ACID Compliant DELETE Statements

A DELETE statement removes rows from a table. Like an UPDATE statement, the absence of a WHERE clause in a DELETE statement deletes all rows in a table. Deleted rows remain visible outside of the transaction scope where it has been removed. However, any attempts to UPDATE those deleted rows are held in a pending status until they are committed or rolled back.

You delete rows when they’re no longer useful. Deleting rows can be problematic when rows in another table have a dependency on the deleted rows. Consider, for example, a customer table that contains a list of cell phone contacts and an address table that contains the addresses for some but not all of the contacts. If you delete a row from the customer table that still has related rows in the address table, those address table rows are now orphaned and useless.

As a rule, you delete data from the most dependent table to the least dependent table, which is the opposite of the insertion process. Basically, you delete the child record before you delete the parent record. The parent record holds the primary key value, and the child record holds the foreign key value. You drop the foreign key value, which is a copy of the primary key, before you drop the primary key record. For example, you would insert a row in the customer table before you insert a row in the address table, and you delete rows from the address table before you delete rows in the customer table.

The figure below shows the logic behind a DELETE statement. Like the INSERT and UPDATE statements, acid, consistency, and isolation properties of the ACID-compliant transaction are managed during the first phase of a 2PC. The durability property is managed by the COMMIT or ROLLBACK statement.

There’s no discussion or diagrams for the MERGE statement because it does either an INSERT or UPDATE statement based on it’s internal logic. That means a MERGE statement is ACID compliant like an INSERT or UPDATE statement.

After sorting out the failures of Oracle Database 11g (11.2.0) on AlmaLinux, I grabbed the Enterprise Linux 9 rlwrap library. The rlwrap is a ‘readline wrapper’ that uses the GNU readline library to
allow the editing of keyboard input for any other command. Input history is remembered across invocations, separately for each command; history completion and search work as in bash and completion word
lists can be specified on the command line.

Installed it with the dnf utility:

dnf install -y rlwrap

It gave me this log file:

Last metadata expiration check: 0:53:30 ago on Fri 02 Dec 2022 01:07:54 AM EST.
Dependencies resolved.
================================================================================================================================
 Package                      Architecture                 Version                             Repository                  Size
================================================================================================================================
Installing:
 rlwrap                       x86_64                       0.45.2-3.el9                        epel                       132 k
 
Transaction Summary
================================================================================================================================
Install  1 Package
 
Total download size: 132 k
Installed size: 323 k
Downloading Packages:
rlwrap-0.45.2-3.el9.x86_64.rpm                                                                  162 kB/s | 132 kB     00:00    
--------------------------------------------------------------------------------------------------------------------------------
Total                                                                                           117 kB/s | 132 kB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
  Preparing        :                                                                                                        1/1 
  Installing       : rlwrap-0.45.2-3.el9.x86_64                                                                             1/1 
  Running scriptlet: rlwrap-0.45.2-3.el9.x86_64                                                                             1/1 
  Verifying        : rlwrap-0.45.2-3.el9.x86_64                                                                             1/1 
 
Installed:
  rlwrap-0.45.2-3.el9.x86_64                                                                                                    
 
Complete!

Last metadata expiration check: 0:53:30 ago on Fri 02 Dec 2022 01:07:54 AM EST. Dependencies resolved. ================================================================================================================================ Package Architecture Version Repository Size ================================================================================================================================ Installing: rlwrap x86_64 0.45.2-3.el9 epel 132 k Transaction Summary ================================================================================================================================ Install 1 Package Total download size: 132 k Installed size: 323 k Downloading Packages: rlwrap-0.45.2-3.el9.x86_64.rpm 162 kB/s | 132 kB 00:00 -------------------------------------------------------------------------------------------------------------------------------- Total 117 kB/s | 132 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Preparing : 1/1 Installing : rlwrap-0.45.2-3.el9.x86_64 1/1 Running scriptlet: rlwrap-0.45.2-3.el9.x86_64 1/1 Verifying : rlwrap-0.45.2-3.el9.x86_64 1/1 Installed: rlwrap-0.45.2-3.el9.x86_64 Complete!

Then, I added this sqlplus function to the student account’s .bashrc file:

sqlplus () 
{
    # Discover the fully qualified program name. 
    path=`which rlwrap 2>/dev/null`
    file=''
 
    # Parse the program name from the path.
    if [ -n ${path} ]; then
        file=${path##/*/}
    fi;
 
    # Wrap when there is a file and it is rewrap.
    if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then
        rlwrap sqlplus "${@}"
    else
        echo "Command-line history unavailable: Install the rlwrap package."
        $ORACLE_HOME/bin/sqlplus "${@}"
    fi
}

Then, I connected to the old, but tiny, footprint of Oracle Database 11g XE for testing, which worked:

Yes, I couldn’t resist. After all Version 11 was the last non-pluggable release and it’s been 11 years since its release. A double lucky 11.

Naturally, you can always use vi (or vim) to edit the command history provided you include the following command in your .bashrc file:

set -o vi

Next, I’ll build a new VM instance with the current version of Oracle Database XE for student testing.

As always, I hope this helps those working with Oracle’s database products.

A quick primer on Perl programs connecting to the MySQL database. It’s another set of coding examples for the AlmaLinux instance that I’m building for students. This one demonstrates basic Perl programs, connecting to MySQL, returning data sets by reference and position, dynamic queries, and input parameters to dynamic queries.

Naturally, a hello.pl is a great place to start:
#!/usr/bin/perl # Hello World program. print "Hello World!\n";
#!/usr/bin/perl # Hello World program. print "Hello World!\n";
After setting the permissions to -rwxr-xr-x. with this command:
chmod 755 hello.pl
chmod 755 hello.pl
You call it like this from the Command-Line Interface (CLI):
./hello.pl
./hello.pl
It prints:
Hello World!
Hello World!

Next, a connect.pl program lets us test the Perl::DBI connection to the MySQL database.

#!/usr/bin/perl
 
# Import libraries.
use strict;
use warnings;
use v5.10;     # for say() function
use DBI;
 
# Print with say() function message.
say "Perl MySQL Connect Attempt.";
 
# MySQL data source name should have a valid database as the
# third argument; this uses the sakila demo database.
my $dsn = "DBI:mysql:sakila";
 
# Local variables to build the connection string.
my $username = "student";
my $password = "student";
 
# Set arguments for MySQL database error management.
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1);   # turn on error reporting via die()           
 
# Create connction with a data source name, user name and password.
my $dbh  = DBI->connect($dsn,$username,$password, \%attr);
 
# Print with say() function valid connection message.
say "Connected to the MySQL database.";