Lock oracle database before running the Delete/Load data scripts

Question

we are running Junit ans Selenium test cases from CI every midnight. We are pre populating the data using the Maven-SQL plugin as following.

          <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>sql-maven-plugin</artifactId>
                <version>1.3</version>
                <executions>
                    <execution>
                        <id>create-database-tables</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <autocommit>false</autocommit>
                            <onError>continue</onError>
                            <srcFiles>
                                <srcFile>../sql/delete_data.sql</srcFile>
                                <srcFile>../sql/load_data.sql</srcFile>
                            </srcFiles>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

we are frequently facing the DB deadlocks due to simultaneous builds by different users. The solution we thought is to lock the database before running the DB scripts.

Can we lock the DB access before running the scripts and unlock it after running the scripts.

Are the deadlocks occurring because multiple users run the above code at the same time? — Rene, May 29 '12 at 07:15
4 teams are working on this project. Due to communication problems, they are running simultaneously. — Anil Kumar C, May 29 '12 at 08:39

score 2 · Accepted Answer · answered May 29 '12 at 18:28

A shared database for testing is never a great idea, presumably you know this which is why you're asking how to restrict access to one user at a time.

Preaching aside..... I'd like to offer a left-field solution of liquibase to manage both the database schema and data population. Has lots of useful features one of which is that it will automatically lock the database and prevent two instance of liquibase interfering with each other.

Example

<project>
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.myspotontheweb.db</groupId>
    <artifactId>liquibase-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <!-- Liquibase settings -->
        <liquibase.url>jdbc:h2:target/db1/liquibaseTest;AUTO_SERVER=TRUE</liquibase.url>
        <liquibase.driver>org.h2.Driver</liquibase.driver>
        <liquibase.username>user</liquibase.username>
        <liquibase.password>pass</liquibase.password>
        <liquibase.changeLogFile>com/myspotontheweb/db/changelog/db-changelog-master.xml</liquibase.changeLogFile>
        <liquibase.promptOnNonLocalDatabase>false</liquibase.promptOnNonLocalDatabase>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.3.162</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.liquibase</groupId>
                <artifactId>liquibase-maven-plugin</artifactId>
                <version>2.0.2</version>
                <executions>
                    <execution>
                        <phase>process-resources</phase>
                        <goals>
                            <goal>update</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

score 0 · Answer 2 · answered May 29 '12 at 07:23

It really depends on how you are using the database. If different users are different oracle users, there should be no problem at all since every user can have their own tables and should not interfere with each other.

If every user is connecting to the same Oracle user, there is a potential conflict. If every test needs exclusive access to all resources, you could build a control table that controls who is allowed access and who is not allowed access. This should be coded in the test preparation and test ending steps to be effective. A simple insert into the table and a check if 'your' insert was the first could be made. Using dbms_lock is also a similar option.

An other way is to use Oracle Resource Manager with a max session limit set to 1 for the Oracle database account that is used for running the exclusive tests. Doing so, every connect denied until a connection slot becomes available again after a test user disconnects.

score 0 · Answer 3 · answered May 31 '12 at 06:44

What you could do is the following:

At the start of the script try to lock a table.

lock table <some_table> in exclusive mode nowait;

This will only succeed if no other process has a lock on this table. If it fails another process is already running.

If you get the lock, run the rest of your script. Be aware that any commit or rollback in your script will end the lock, so only commit or rollback at the very end of the script.

Also make sure you lock a table that is there for the sole purpose of having something to lock in order to exclusively run your scripts.

Lock oracle database before running the Delete/Load data scripts

3 Answers3

Example

Linked