| Author |
Message
|
| RangaKovela |
 Posted: Thu Sep 10, 2015 3:31 am Post subject: IIB 9.0 - SQL Server -Stored Procs - Tranactionality |
 |
|
Apprentice
Joined: 10 May 2011
Posts: 38
|
Hi WBI experts,
Environment.
IIB 9.0
SQL Server with Stored procedures inserts
We have a flow which calls at least 5 different stored procedures which does insert in database tables and finally put the message in the queue. If flow fails to put message in queue due to variety of reasons (like queue full or queue closed) whatever inserts done by SPs earlier should be rolled back. However this is not happening on the SQL server.
Compute mode transactionality is set to Automatic to commit or rollback the DB changes based success or failure of Message flow
MQ Input node transaction mode is set to Yes
Message are marked as Persistent. DEFPSIST of queue is marked as Yes
Not sure what is the issue here. Can some one advice?
Thanks in advance, |
|
| Back to top |
|
 |
| maurito |
| Posted: Thu Sep 10, 2015 3:57 am Post subject: Re: IIB 9.0 - SQL Server -Stored Procs - Tranactionality |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| RangaKovela wrote: |
Hi WBI experts,
Environment.
IIB 9.0
SQL Server with Stored procedures inserts
We have a flow which calls at least 5 different stored procedures which does insert in database tables and finally put the message in the queue. If flow fails to put message in queue due to variety of reasons (like queue full or queue closed) whatever inserts done by SPs earlier should be rolled back. However this is not happening on the SQL server.
Compute mode transactionality is set to Automatic to commit or rollback the DB changes based success or failure of Message flow
MQ Input node transaction mode is set to Yes
Message are marked as Persistent. DEFPSIST of queue is marked as Yes
Not sure what is the issue here. Can some one advice?
Thanks in advance, |
maybe the stored procedures are doing a commit ? |
|
| Back to top |
|
|
| RangaKovela |
| Posted: Thu Sep 10, 2015 4:21 am Post subject: |
|
|
Apprentice
Joined: 10 May 2011
Posts: 38
|
| Quote: |
| maybe the stored procedures are doing a commit ? |
Cross checked with DBA. There are no transactional statements like (COMMIT or ROLLBACK) |
|
| Back to top |
|
|
| maurito |
| Posted: Thu Sep 10, 2015 4:32 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| RangaKovela wrote: |
| Quote: |
| maybe the stored procedures are doing a commit ? |
Cross checked with DBA. There are no transactional statements like (COMMIT or ROLLBACK) |
do you have any error handlers, esql handler, failure terminals attached, and where ?
have you run a usertrace debug mode to see what is happening ? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Sep 10, 2015 4:33 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| RangaKovela wrote: |
| Quote: |
| maybe the stored procedures are doing a commit ? |
Cross checked with DBA. There are no transactional statements like (COMMIT or ROLLBACK) |
Have you verified this for yourself?
Startyp SQLServer Manangmeent studio
start a new query
add a delect to check some data that would get updated/replaced
start a unit of work
call the SP with some valid data
finish the UOW
check the data
Do the same but issue a rollback instead of commiting the data.
If the data is truly rolled back to what it was before then it might be time for a PMR.
But you need to do this sort of validation first.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Sep 10, 2015 4:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Rolllback doesn't happen unless the MQInput node completes processing the message with an exception.
That is, you need to make sure anything connected to the MQInput node catch terminal doesn't end the flow without another Throw node.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| nelson |
| Posted: Thu Sep 10, 2015 4:40 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
A few days ago I faced the same issue and in fact there was a commit within a SP.
On ESQL, I did a manual insert after the last insert and in fact this particular line was roll backed, but the previous SP don't, because casually, the last SP was issuing a commit.
I should double check every SP..
 |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Sep 10, 2015 5:01 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| nelson wrote: |
A few days ago I faced the same issue and in fact there was a commit within a SP.
: |
This was why I suggested verifying that there isn't some commit hidden several levels down in the SP chain.
Once the OP is 10000% certain that there isn't a commit being actioned then they could try ODBC trace and for the two conditions see what was actually sent to the DB.
This all takes time and patience.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
|
|
