Explicit locking

SELECT FOR UPDATE

Complex transaction management is usually only necessary within database applications where the SQL statements are embedded into structured code such as Java or C#. In an application program there may be several processing operations between the retrieval of a value from the database and the modification of the records. For this reason, it is important that there is a way to tell the DBMS that you intend to update certain rows in a table at some point in the future. This is done with an additional clause in the SQL SELECT statement.

An explicit lock can be obtained on all of the rows returned by a query by adding a FOR UPDATE clause at the end of the query. The example below illustrates how to lock the row in the hr.countries table that we created earlier:

    SELECT *
    FROM   countries
    WHERE  country_id = 'ES'
    FOR UPDATE;

Log into as the HR user in bot SQLPlus and SQL Developer as before, and run this statement in SQLPlus. You will see the results of the query as usual, but the difference is that you have now locked that row of the table. You can see the effect of this by running the following statement in the SQL Developer interface:

    UPDATE countries
    SET    country_name = 'Espana'
    WHERE  country_id = 'ES';

Release the lock in SQL*Plus by issuing either the COMMIT or ROLLBACK statement, then try the update again.

LOCK TABLE

Sales databaseIf you are not selecting from the table with a view to updating it later, you can use the LOCK TABLE command. This has exactly the same effect of giving you exclusive control over the locked tables until a COMMIT or ROLLBACK statement is issued. Applied to the e-commerce example, assuming an order with two lines, the full transaction would look like this:

    LOCK TABLE invoice;
    LOCK TABLE product;

    INSERT INTO invoice …

    INSERT INTO invoice_line …
    UPDATE product SET prod_quantity = …

    INSERT INTO invoice_line …
    UPDATE product SET prod_quantity = …

    UPDATE invoice SET inv_total = …

    COMMIT;