July 28, 2005 at 5:48 am
Hi,
Oracle supports Autonomous transaction.
i.e It can have its local transaction which can commit or rollback without effecting the outer transaction.
Problem is sqlserver is that it simply ignores the commit given by inner transaction and commits/rollbacks only by outer transaction.
Thanks,
Netaji
July 28, 2005 at 7:04 am
If I understand you correctly, then to my knowledge, what you ask for is not directly supported.
However, you may find useful this little workaround I use to make log entries within a transaction that is being rolled back...
-- LOG PROCEDURE
/*
Creates a log entry within a transaction without being rolled back if the transaction is rolled back
*/
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'spMakeLogEntry' AND [type] = 'P')
DROP PROCEDURE dbo.spMakeLogEntry
GO
CREATE PROCEDURE dbo.spMakeLogEntry
@entry varchar(255)
WITH ENCRYPTION
AS
IF (@entry IS NULL) OR (@entry = '')
BEGIN
PRINT 'spMakeLogEntry requires the @entry varchar(255) parameter to be supplied and not to be blank or NULL.'
RETURN
END
DECLARE @oSQLServer int
DECLARE @oDatabase int
DECLARE @CurrentDB varchar(255)
DECLARE @sql varchar(400)
DECLARE @Error int
DECLARE @ErrorMsg varchar(255)
DECLARE @Source varchar(255)
DECLARE @oQR int
EXEC sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
EXEC sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME, 'username', 'password'
SELECT @CurrentDB = 'Databases(' + RTRIM(DB_NAME()) + ')'
EXEC sp_OAGetProperty @oSQLServer, @CurrentDB, @oDatabase OUT
SET @sql = 'INSERT DW_Log (DW_Log_Entry) VALUES (''' + @entry + ''')'
EXEC @Error = sp_OAMethod @oDatabase, 'ExecuteImmediate', NULL, @sql
IF @Error 0
BEGIN
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
PRINT @ErrorMsg
END
EXEC sp_OADestroy @oSQLServer
EXEC sp_OADestroy @oDatabase
GO
July 28, 2005 at 7:40 am
You might be able to achieve what you want if you use save points. The code below may not be nice, but it can be used to begin, commit and rollback a transaction, whether or not your code is enclosed inside a transaction. I hope it is useful to you.
-- INSTEAD OF BEGIN TRANSACTION
if @@TranCount >= 1
begin
select @SavePoint = 'SavePoint' + convert(varchar, @@NestLevel)
save transaction @SavePoint
end
else
begin
select @SavePoint = ''
begin transaction
end
-- INSTEAD OF ROLLBACK TRANSACTION
if @SavePoint = ''
rollback transaction
else
rollback transaction @SavePoint
-- INSTEAD OF FOR COMMIT TRANSACTION
if @SavePoint = ''
commit transaction
July 28, 2005 at 7:51 am
Another workaround is to use Table Datatype variables which are not affected by Transaction Handling statements!
* Noel
July 29, 2005 at 8:12 am
Hi,
Thanks for your replies.
My actual problem is :
A procedure code:
locks the table say xyz in the transaction opened in the procedure.
After doing some operations I want to release this table. This can done either by commit or rollback transaction.
If this procedure is called directly from query analyzer it works.
If you call the same procedure within an opened transaction, then procedure opens one more transaction(Nested). Now If I say commit to inner transaction. It will not effect the table i.e, it will not lock the table until the outer transaction is commited/rollback.
But I need to release the lock with in inner transaction.
So, please help me in this issue.
Thanks,
Netaji
August 1, 2005 at 2:06 am
I am still not sure I understand you correctly, but as long as the outer transaction can be rolled back, I don't think you can avoid having a lock on a table that you have changed (I assume that how you obtain a lock on table xyz) during the (outer) transaction.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply