help understanding runInTransaction

General Discussion
1

Hello All,


I’ve got a set of database scripts generated by DBGhost for an MS SQL database. My requirement for now is to automate our builds via liquibase and its maven plugin using these files with little or no change to the current scripts.


After some fits with file encoding, I’ve now got all of the DDL scripts working using changeSet with sqlFile without having to modify any of the files. GREAT!


But, I’m struggling when it comes to loading up the static data scripted via DBGhost. These DBGhost “Static Data” scripts handle identity columns, add error handling, transactions, and some other various bits like rebuilding indices, etc. Unfortunately, the transactions in these scripts are interfering with liquibase in a way I don’t quite understand.


If I leave runInTransaction=“true” (the default), then running mvn liquibase:update results in the following build error:


<blockquote class=“webkit-indent-blockquote” of these scripts if/when they are re-generated by DBGhost. I also realize that DBGhost does provide products that perform some similar functions to liquibase but our team prefers cross-platform, OSS so we chosen to eschew further licensing of DBGhost.


TIA,

Doug

2

Bit of an update.


We’ve decided it’s OK to use our existing DBGhost as only a baseline and try doing things the “liquibase way”, but I was still having problems with inserting data. Same behavior as noted above, but with a much simple change set without it’s own transactions.


Turns out the problem is related to “SET NOCOUNT ON” as the LockService relies on the count of rows changed to determine whether to the lock was released successfully.


Carry on…

3

Yeah, we’ve ran into issues with the lock table when set nocount on is set. There is a bug for it, but no work-around yet besides making sure set nocount is off when the lock table is accessed. 


For reference, you shouldn’t have to deal with the runInTransaction attribute, it is best to have each changeSet run in a transaction rather than autocommit after each statement (for statement types the DB doesn’t already autocommit). There are some databases that have problems with some statements running in a transaction, but sqlserver is just fine.


Nathan

4

Can you please post which all database are having isssue wiht trasaction?