Implement Transaction Integrity with Sybase 12.5

By S.Luann


With Sybase 12.5, (SQL Server 2005 is the same), failing of a statement in a batch doesn't necessarily abort the batch. This behavior forces developer to check the running status of each DML statement that makes changes to the database so that to determine whether to commit or rollback a transaction.

In order to investigate this issue, I prepared two tables (List 1 and List 2) and a stored procedure (List 3).

List 1.

CREATE TABLE dbo.test
(
id decimal(38,0) NOT NULL,
name varchar(2048) NOT NULL,
CONSTRAINT test_340193231
FOREIGN KEY (id)
REFERENCES dbo.test2 (id)
)

List 2.

CREATE TABLE dbo.test2
(
id decimal(38,0) NOT NULL,
CONSTRAINT test2_3241931742
PRIMARY KEY NONCLUSTERED (id)
)


List 3.

CREATE PROCEDURE p_test

AS
BEGIN

BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, NULL) -- CAUSE EXCEPTION BECAUSE NAME COLUMN IS NOT NULLABLE

INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS

COMMIT TRAN

END

In table test2 only one record with id equals to 100.

Executing this stored procedure in List 3 results an exception saying null is not allowed, and no records has been inserted. No transaction voilation happend.

Then run List 4, It causes an exception saying referencial integrity problem, and there is a record inserted to table test. It violates the transaction integrity! This behavior tells that a run-time error doesn't stop the batch running. To test this behavior, I execute List 5. which uses a parameter rather than a static value, It inserted a record and violates the transaction as well.

List 4.

CREATE PROCEDURE p_test

AS
BEGIN

BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(99, 'Non null value') -- CAUSE EXCEPTION BECAUSE ONLY 100 WON'T CAUSE REFERENCIAL VIOLATION

INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS

COMMIT TRAN

END


List 5

CREATE PROCEDURE p_test
(
@name VARCHAR(50) = NULL
)
AS
BEGIN


BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, @name) -- CAUSE EXCEPTION if the parameter is passing null

INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS

COMMIT TRAN
END
GO

EXECUTE p_test
GO

Why in the first case it doesn't violate the transaction? I traced step by step and found the exception in the first case comes from compile-time, because it doesn't even run into the procedure. It is understandable though that a static value will be checked out if violating any rules and constraints.


The Conclusion
To get the transaction work, I check the error status after each DML statement which makes changes to the database. See List 6.

List 6.

CREATE PROCEDURE p_test
(
@name VARCHAR(50) = NULL
)
AS
BEGIN

BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, @name) -- CAUSE EXCEPTION if the parameter is passing null
IF(@@error<>0) GOTO fail

INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS
IF(@@error<>0) GOTO fail

COMMIT TRAN
RETURN(0)

fail:
ROLLBACK TRAN
RETURN(1)
END
GO
EXECUTE p_test
GO
EXECUTE p_test 'any name'
GO