sql server - RSQLServer: Cannot begin a nested transaction after dbRollback -

background

in database have uniqueness constraints. if data breaks 1 of conditions, error message violation of unique key constraint.

i use trycatch in code, capture error , return meaningful message user. far good.

however, if try run new transaction on server after having captured error, error message saying cannot begin nested transaction.

my findings

i traced error down, , figured when dbrollback called (either explicitly, or within withtransaction) 1 cannot submit new dbbegin anymore (either explicitly or implicitly via dbwritetable , friends).

what need unstuck, run dbcommit, allowed run dbbegin.

looking @ code of dbcommit , dbrollback see in former case setautocommit set true, signals dbbegin not nesting transactions. not case dbrollback:

getmethod("dbcommit", "sqlserverconnection") # method definition: #  # function (conn, ...)  # { #     rjava::.jcall(conn@jc, "v", "commit") #     rjava::.jcall(conn@jc, "v", "setautocommit", true) #     true # } # <environment: namespace:rsqlserver>      getmethod("dbrollback", "sqlserverconnection") # method definition: #  # function (conn, ...)  # { #     rjava::.jcall(conn@jc, "v", "rollback") #     true # } # <environment: namespace:rsqlserver> 

question

so question is: supposed behavior? is, suppose run manual dbcommit after operation rolled back, or bug?

code

 library(dbi)  library(rsqlserver)  db <- dbconnect(...)  dbbegin(db)  dbcommit(db)   dbbegin(db) # works  dbrollback(db)   dbbegin(db) # not work   dbcommit(db) # workaround  dbbegin(db) # works again 

wiki