Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Few rows in a table disappear

Author  Topic 

vikrant99
Starting Member

2 Posts

Posted - 2006-09-08 : 06:19:39
In our SQL Server 2000 database there are several tables. One 'LatestBillNumber' table stores the latest Bill number. It has one row and one column. The 'Transaction' table has transactions and one of the column in this table is BillNumber.

The process works as follows.

If there are 10 new transactions ready for bill printing then the lastest bill number if fetched from the LatestBillNumber table and assigned to the application's variable. Next the latest bill number is incremented by 10 and committed. For each of the 10 new transactions, the bill numbers are assigned sequentially, starting from the bill number fetched and stored in the applications variable. This operation is then committed. Then these 10 rows are selected for bill printing.

At times it is observed that these 10 rows disappear from the Transaction table and the 'LatestBillNumber' table has the earlier value.

It is also observed that other tables that were used by other applications for insert, update or delete during this period have the desired rows intact.

As the bill printing process happens after committing the updates in both these tables, we wonder what made these 10 rows disapper. The transaction table also has a trigger on delete that moves deleted rows to another table as an audit trail. But this trigger is not fired in such cases.

Our application is working at many sites but this behaviour was reported at two sites on 3-4 occassions.

Any clues??

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-08 : 09:47:24
Are the database changes made by the application directly or does the application call sql server stored procedures?

>>Next the latest bill number is incremented by 10 and committed
I'm not sure if you are referring to a COMMIT of an explicit sql transaction or you if you mean you are sending an UPDDATE statement to the server from the application. Are explicit sql server transactions being used by either stored procedures of the application itself?

The answer to my first question will lead to other questions. But it sounds like maybe the update of your LatestBillNumber table as well as the transaction table are all handled in the same Sql Server Transaction. So if an error occurs or a violation of business rules then a ROLLBACK may be issued for the entire (sql server) transaction.

What happens when 2 users try to bill at the same time? In other words user1 and user2 both get the same latestBillNumber?

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -