Re: how to rollback stored-rountings

[Roland Bouman]

What is the problem exactly?

I mean, If I have a table like this:

CREATE TABLE `bla` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(64) NOT NULL,
`dummy` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


and a procedure like this:

delimiter go

create procedure p_bla()
begin
set autocommit = 0;
insert into bla (name) values ('Roland');
commit;
insert into bla (name) values ('Bouman');
rollback;
end;
go

a call to that procedure:

call p_bla()
go

works exactly as expected on my 5.0.17-nt; that is, the first row is commited, and the second one is rolled back.

Or am I missing something here?

I think the restriction does hold for triggers (which should not be a problem)

BTW, what do you want to achieve here?

I think there should be a very compelling reason to want a commit or a rollback inside a procedure. In my opinion, it's better to let the caller decide wheter to commit or rollback; just as it is the case for 'normal' DML statements, like INSERT, DELETE etc.

By hardwiring the COMMIT/ROLLBACK inside the proc, the caller should be aware on beforehand that he's potentially rolling back all the other work in the session too. Things are even worse when the procedure can throw an exception - did the rollback occur already, or did it not, because of the exception.

So, I am very curious why you want to rollback inside the proc. Please let me know, Im interested.

Roland



Edited 1 time(s). Last edit at 01/11/2006 04:33AM by Roland Bouman.