Transaction management in SQL -- Sybase example

You may also like:

• Common Hibernate mistakes - accessing objects outside of a transaction
• SQL Interview Questions and Answers

Q. How do you perform transaction management in SQL stored procedures?
A. Handled with begin, commit, and rollback 'tran' commands in Sybase server.

Q. Why is it important?
A. It is important to leave the database in a consistent state.

Q. Can you give an example?
A. For example, as demonstrated below, if you are going to delete 21 records from a database table, you can use transaction management to ensure that a GIVEN COUNT say 21 records are either deleted or rolled back. Partially deleting records can leave the database  in inconsistent state.

print 'Before delete from employee_table'
print '-------------------------------'

DECLARE  @EMPLOYEE_IN_CLAUSE  varCHAR(10000)
Select @EMPLOYEE_IN_CLAUSE = "'John', 'Joseph'"

exec ('select * from semployee_table where first_name in (' +  @EMPLOYEE_IN_CLAUSE +  '))
exec ('select count(*) as deleted_transact_records_count from employee_table where first_name in (' +  @EMPLOYEE_IN_CLAUSE + ') )

declare @rowcount int
select  @rowcount = 0

--transaction starts
begin tran

--Deletion
print 'delete some records from employee_table'
exec ('delete from sd_wrap..employee_table where first_name in (' +  @EMPLOYEE_IN_CLAUSE + ') and bean_name = ''TransactDetail''')

--number of rows deleted
select @rowcount = @rowcount + @@rowcount

--commit or rollback
if @rowcount = 21
begin
   commit tran
   print 'success'
end
else
begin
   rollback tran
   print 'failed'
end


print 'After delete from employee_table'
print '-------------------------------'

exec ('select * from employee_table where first_name in (' +  @EMPLOYEE_IN_CLAUSE +  '))
exec ('select count(*) as deleted_transact_records_count from sd_wrap..employee_table where first_name in (' +  @EMPLOYEE_IN_CLAUSE + ') )

go

In Sybase, @@rowcount variable returns the number of rows affected by the query. This post also demonstrates a production ready script with proper print statements, transaction management, etc to perform a basic DELETE operation. Same thing is true for insert and update operations.

Q. Do you require any other script if the above deletion causes unexpected  issue?
A. Yes, you need a rollback script with 21 insert statements to revert the changes. In other words script to put the deleted data back.

Q. Is there a smarter way to generate the insert script or will you type them in one by one?
A. There is a smarter way using a select statement to generate all the 21 insert statements with the following query.

SELECT 'INSERT INTO employee_table ( first_name, surname, salary) VALUES (' 
|| '''' || fist_name || ''' '
|| ',''' || surname || ''' '
|| ',''' || salary 

|| ')'

FROM employee_table 
WHERE first_name in ('John', 'Joseph');

Another SQL generation tip with Excel spreadsheet.

You may also like:

• Common Hibernate mistakes - accessing objects outside of a transaction
• SQL Interview Questions and Answers