File under “I Learned This One the Hard Way”: When configuring the service method details during SmartObject creation, consider leaving the Transaction option set at “Continue,” and handling your transaction goop closer to source.
For example, if you’re writing a SmO for a stored procedure that will be doing some updates, dust off your old PL/SQL or SQL Server books and put that transactional syntax to good use.
Trying to handle it up in the SmO could create problems.
Who has two thumbs and and had to have a DBA called in at 11:30 at night to unlock a table because he set a SmO to “Rollback?” *This* guy!
The thinking is that when the data passed into the SmO didn’t pass muster, the SmO issued a rollback command to the db. The table essentially locked because the RDBMS was looking for a transaction that didn’t exist — put another way, it seems the SmartObject won’t start a transaction for you. If this is true, why handle this in your data adapter at all?
Handle your exceptions closer to source, keep that Transaction dropdown set at “Continue,” and get on with your life.