SQL Transactions Question
-
Hi All,
Can someone shed some light on this issue for me, as I am very confused on why this is happening.
By running the following code the last Commit is irrelevant as only 1 transaction is active.
This makes sense as I only have 1 Begin Transaction
SET IMPLICIT_TRANSACTIONS OFF --AUTO COMMIT IS ON
--SET IMPLICIT_TRANSACTIONS ON --AUTO COMMIT IS OFF
PRINT @@TRANCOUNT
BEGIN TRANSACTION
PRINT @@TRANCOUNT
commit tran
PRINT @@TRANCOUNT
commit tran
PRINT @@TRANCOUNT
However when I run the following code the last Commit is required as 2 transaction are active.
This I do not understand, as the directly after Begin Transaction 2 transactions are active
--SET IMPLICIT_TRANSACTIONS OFF --AUTO COMMIT IS ON
SET IMPLICIT_TRANSACTIONS ON --AUTO COMMIT IS OFF
PRINT @@TRANCOUNT
BEGIN TRANSACTION
PRINT @@TRANCOUNT
commit tran
PRINT @@TRANCOUNT
commit tran
PRINT @@TRANCOUNT
Also when I run the 2nd code inside a Job on SQL, it will work, but the first one errors. So I commented out Implicit_Transaction code inside the JOB and the following happened, the 2nd code works but the first one errors. From this is
The job seems to run on the context that Implicit_Transaction is ON.
So what I am seeking is the following:-
- An explanation on why the second code snippet tells me two transactions are running?
- Confirmation that Jobs run in the context of Implicit_Transaction being ON.
- How to code properly inside a stored procedure with Implicit_Transaction OFF in relation to Transactions, this scenario arose when I trying to run an overnight admin SP where the latter half of the SP is stopped by running due to locks in the first half of the SP. In my scenario the first half once confirmed to be correct can have all their transactions commited (if possible) to enable the 2nd half of the SP to run. The 2nd half been a BCP of a table populated in the first half of the SP.
- Also if my memory is correct inside stored procedures even if there are nested Transactions when a Commit is seen the inner transactions as well as the outer transactions are all commited (query relates to my issue in point 3 above). So if this is indeed the case, what is the best approach to placing various transactions inside an SP.
Many thanks for your help, I really appreciate it.
Darren
-
http://www.devx.com/tips/Tip/13337
By setting Implicit transaction on, simply executing a statement implies a begin tran. Check out the Article
Prettly clear in
http://msdn2.microsoft.com/en-us/library/ms187807.aspx
I have not experienced an issue with implicit transaction on.
I have not had issue with Implicit transactions within a job. So I'm not sure what the inital status of it is.
About how to write you stored procedure all I can do is offer how transactions work.
Books Online describes it well
BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. So when your done with the data in the first part of your proc Commit it. I dont have your code to know if you need nested transactions, but BCP is a different connection, so you need to make sure all transactions in your proc are closed.
-
Hi Ray M
Many thanks for taking the time and effort to post a reply, I really appreciate it.
From the information contained in your links and from subsequent testing I now know the reason why when Implicit_Transactions is set to ON
that there are 2 Active Transactions.
As it says you dont need to explicity say BEGIN TRANSACTION prior to whatever transaction you are about to do, but if you do use it
the statement BEGIN TRANSACTION and the subsequent Statement are classified as 2 separate transactions.
Now all I need to do is
A: Get my BCP to export the contents of a table where the lock has been released but that the code works both in an
SP run from an ISQL session and from a SQL Server Job.
Below is a rough draft of my current scenario
Ill use North wind as an Example
--drop table example
CREATE TABLE example (
categoryID VARCHAR(6),
CategoryName VARCHAR(15)
 
Currently my code runs something like this
DECLARE @iRecordsExist INT
DECLARE @sSQL VARCHAR(900)
BEGIN Transaction Test
PRINT 'Tran count under Begin Transaction: ' + CAST(@@TRANCOUNT AS VARCHAR)
INSERT INTO example
SELECT RIGHT('000000'+(CAST(categoryID AS VARCHAR)),6),
CategoryName
FROM categories
SELECT @iRecordsExist = COUNT(*)
FROM example
IF @iRecordsExist > 0
BEGIN
--Some additional data checks take place here
PRINT 'Tran count after data checks are good: ' + CAST(@@TRANCOUNT AS VARCHAR)
COMMIT Tran Test
/* ------ */ COMMIT Tran /* ------ */
PRINT 'Tran count after Committing Transaction Test: ' + CAST(@@TRANCOUNT AS VARCHAR)
SET @sSQL = 'bcp northwind..example out C:\northwind.txt -CACP -m0 -c -t -r \n -e C:\NorthindErrorFile.txt -o C:\NorthwindLogFile -SNbmerdrkelly1 -T'
--PRINT @sSQL
EXEC master..xp_cmdshell
@sSQL
END
ELSE
BEGIN
Print 'No work today'
END
Summation on how the above works on my system
The BCP sql will not run as the table example is locked due to it been populated.
To make the BCP run I need to place into the SQL another commit prior to the BCP in the above case that commit is
/* ------ */ COMMIT Tran /* ------ */
Now when the above code is run from a ISQL session it works perfectly,
however when I now run it from inside a SQL Job it errors, too many commits so I need to remove this line
/* ------ */ COMMIT Tran /* ------ */
to make it work.
So I just dont know how to make the code Universal to make it run the same from an ISQL session and from inside a
SQL Job.
Again Ray thanks for your response.
Darren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply