lock is not released if nocount is on for sql server 2008

General Discussion
1

There is a bug how liquibase releases the lock after running sql commands.
Because of some unknown reason, it is relying on nocount setting in case of sql server. If nocount is on then lock is not released. Please verify bug and fix in coming version…

a sample changeset to reproduce the bug is:

  1. <?xml version="1.0" encoding="utf-8"?>
    <databaseChangeLog
        xmlns=“http://www.liquibase.org/xml/ns/dbchangelog”
        xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”
        xsi:schemaLocation=“http://www.liquibase.org/xml/ns/dbchangelog
            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd”>

       
           
            set nocount on
           
       

output:
  1. C:\xxxx>mvn
    [INFO] Scanning for projects…
    [INFO] ------------------------------------------------------------------------
    [INFO] Building DB.LB.
    [INFO]    task-segment: [test]
    [INFO] ------------------------------------------------------------------------
    [INFO] [resources:resources]
    [WARNING] Using platform encoding (Cp1252 actually) to copy filtered resources, i.e. build is platform dependent!
    [INFO] skip non existing resourceDirectory C:*    \src\main\resources
    [INFO] [liquibase:update {execution: default}]
    [INFO] ------------------------------------------------------------------------
    [INFO] Loading artfacts into URLClassLoader
    [INFO]   artifact: file:/C:/    /.m2/repository/net/sourceforge/jtds/jtds/1.2/jtds-1.2.jar
    [INFO]   artifact: file:/C://.m2/repository/org/liquibase/liquibase-maven-plugin/2.0.1/liquibase-maven-plugi
    n-2.0.1.jar
    [INFO]   artifact: file:/C:    ******/target/classes
    [INFO]   artifact: file:/C://target/test-classes
    [INFO] ------------------------------------------------------------------------
    [INFO] Settings----------------------------
    [INFO]     driver: net.sourceforge.jtds.jdbc.Driver
    [INFO]     url: jdbc:jtds:sqlserver://    /    ;progname=JTDS;appName=LB;useLOBs=false;instance=
    [INFO]     username: ****
    [INFO]     password:
    [INFO]     use empty password: false
    [INFO]     properties file: null
    [INFO]     properties file will override? false
    [INFO]     prompt on non-local database? false
    [INFO]     clear checksums? false
    [INFO]     changeLogFile: baseline/install.xml
    [INFO]     context(s): null
    [INFO]     number of changes to apply: 0
    [INFO]     drop first? false
    [INFO] ------------------------------------------------------------------------
    [INFO] Executing on Database: jdbc:jtds:sqlserver://    /    ;progname=JTDS;appName=LB;useLOBs=false;instance=
    INFO 17/03/11 4:21 PM:liquibase: Successfully acquired change log lock
    INFO 17/03/11 4:21 PM:liquibase: Creating database history table with name: [dbo].[DATABASECHANGELOG]
    INFO 17/03/11 4:21 PM:liquibase: Reading from [dbo].[DATABASECHANGELOG]
    INFO 17/03/11 4:21 PM:liquibase: Reading from [dbo].[DATABASECHANGELOG]
    INFO 17/03/11 4:21 PM:liquibase: ChangeSet ./changelog/error.change.xml::1::erroruser ran successfully in 3ms
    liquibase.exception.LockException: liquibase.exception.LockException: Did not update change log lock correctly.

    0 rows were updated instead of the expected 1 row using executor liquibase.executor.jvm.JdbcExecutor there are 1 rows in
     the table
            at liquibase.lockservice.LockService.releaseLock(LockService.java:152)
            at liquibase.Liquibase.update(Liquibase.java:116)
            at org.liquibase.maven.plugins.LiquibaseUpdate.doUpdate(LiquibaseUpdate.java:31)
            at org.liquibase.maven.plugins.AbstractLiquibaseUpdateMojo.performLiquibaseTask(AbstractLiquibaseUpdateMojo.java
    :24)
            at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:302)
            at org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:483)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoals(DefaultLifecycleExecutor.java:678)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoalWithLifecycle(DefaultLifecycleExecutor.java:54
    0)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoal(DefaultLifecycleExecutor.java:519)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoalAndHandleFailures(DefaultLifecycleExecutor.jav
    a:371)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeTaskSegments(DefaultLifecycleExecutor.java:332)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.execute(DefaultLifecycleExecutor.java:181)
            at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:356)
            at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:137)
            at org.apache.maven.cli.MavenCli.main(MavenCli.java:356)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:597)
            at org.codehaus.classworlds.Launcher.launchEnhanced(Launcher.java:315)
            at org.codehaus.classworlds.Launcher.launch(Launcher.java:255)
            at org.codehaus.classworlds.Launcher.mainWithExitCode(Launcher.java:430)
            at org.codehaus.classworlds.Launcher.main(Launcher.java:375)
    Caused by: liquibase.exception.LockException: Did not update change log lock correctly.

    0 rows were updated instead of the expected 1 row using executor liquibase.executor.jvm.JdbcExecutor there are 1 rows in
     the table
            at liquibase.lockservice.LockService.releaseLock(LockService.java:140)
            … 22 more
    [ERROR] liquibase.exception.LockException: Did not update change log lock correctly.

    0 rows were updated instead of the expected 1 row using executor liquibase.executor.jvm.JdbcExecutor there are 1 rows in
     the table
    liquibase.exception.LockException: liquibase.exception.LockException: Did not update change log lock correctly.

    0 rows were updated instead of the expected 1 row using executor liquibase.executor.jvm.JdbcExecutor there are 1 rows in
     the table
            at liquibase.lockservice.LockService.releaseLock(LockService.java:152)
            at liquibase.lockservice.LockService.forceReleaseLock(LockService.java:194)
            at liquibase.Liquibase.forceReleaseLocks(Liquibase.java:575)
            at org.liquibase.maven.plugins.AbstractLiquibaseMojo.cleanup(AbstractLiquibaseMojo.java:429)
            at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:309)
            at org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:483)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoals(DefaultLifecycleExecutor.java:678)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoalWithLifecycle(DefaultLifecycleExecutor.java:54
    0)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoal(DefaultLifecycleExecutor.java:519)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoalAndHandleFailures(DefaultLifecycleExecutor.jav
    a:371)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeTaskSegments(DefaultLifecycleExecutor.java:332)
            at org.apache.maven.lifecycle.DefaultLifecycleExecutor.execute(DefaultLifecycleExecutor.java:181)
            at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:356)
            at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:137)
            at org.apache.maven.cli.MavenCli.main(MavenCli.java:356)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:597)
            at org.codehaus.classworlds.Launcher.launchEnhanced(Launcher.java:315)
            at org.codehaus.classworlds.Launcher.launch(Launcher.java:255)
            at org.codehaus.classworlds.Launcher.mainWithExitCode(Launcher.java:430)
            at org.codehaus.classworlds.Launcher.main(Launcher.java:375)
    Caused by: liquibase.exception.LockException: Did not update change log lock correctly.

    0 rows were updated instead of the expected 1 row using executor liquibase.executor.jvm.JdbcExecutor there are 1 rows in
     the table
            at liquibase.lockservice.LockService.releaseLock(LockService.java:140)
            … 22 more
    [INFO] ------------------------------------------------------------------------
    [INFO]
    [INFO] [compiler:compile]
    [INFO] Nothing to compile - all classes are up to date
    [INFO] [resources:testResources]
    [WARNING] Using platform encoding (Cp1252 actually) to copy filtered resources, i.e. build is platform dependent!
    [INFO] skip non existing resourceDirectory C:*    \db\src\test\resources
    [INFO] [compiler:testCompile]
    [INFO] Nothing to compile - all classes are up to date
    [INFO] [surefire:test]
    [INFO] No tests to run.
    [INFO] ------------------------------------------------------------------------
    [INFO] BUILD SUCCESSFUL
    [INFO] ------------------------------------------------------------------------
    [INFO] Total time: 2 seconds
    [INFO] Finished at: Thu Mar 17 16:21:13 EDT 2011
    [INFO] Final Memory: 18M/218M
    [INFO] ------------------------------------------------------------------------
    C:*    **************>
the workaround i am using if I run a sql which requires nocount on is to put this line in the end of changeset

set nocount off

2

The reason it needs to get a count back is that liquibase uses the number of rows updated to determine if the lock was released correctly or not. 

Is there a way to determine if nocount mode is on in sqlserver to modify that behavior? I don’t want to change it for everyone.


Nathan

3

After some googling I found nocount setting is stored in a @@OPTIONS variable as a bit flag.

If you run this query with nocount on or off it will give you right answer (tested in SQL 2008):

  1. IF ( (512 & @@OPTIONS) = 512 ) PRINT ‘ON’ ELSE PRINT 'OFF’

but also I think that nocount is a local option… so if you set nocount to off before your update query you may get around to this issue. not sure if you need to reset the option back to original or if sql server automatically sets it back.

4

Liquibase expects at least one row in DATABASECHANGELOGLOCK table. Insert one row in this  table with values of Id=1 and Locked=0. This will resolve the issue.

 

Rahul