Home » Database » DB » Bug #181

[2003-11-02 03:56 UTC] gunther at ultraconsulting dot com

Description:
------------
When during a transaction, one table (T1) update, one table (T2) insert, another transaction occurs with the same tables T1, T2, one update does not happen.

The update is a simply increment of a sequence number and due to the problem the number is used several times causing severe problems in the data integrity.
MS-SQL Server 2000 is the database.
pear_info:
PEAR: 1.0.1
DB: 1.4b1
Test scenario: use 2 PCs each with a browser
1. Create a MS-SQL DB Test20031101
2. Initialize DB with initDB.php script
3. First PC: visit url http://yourpath/testDBTransactions.php?cid=1
4. Other pc: http://yourpath/testDBTransactions.php?cid=1 with a delay not more than 2 seconds after first PC
5. Check DB content and see if contract_order_seq_id was incremented by one for each contract

Reproduce code:
---------------
I am currently trying to create a small script to reproduce the error.

Expected result:
----------------
Properly incremented sequence numbers

Actual result:
--------------
Intermittendly not incremented sequence numbers

[2003-11-03 04:08 UTC] gunther at ultraconsulting dot com

A complete test script is available at http://www.ultraconsulting.com/bugs/testDBTransactions.zip (5kB)

[2004-04-09 03:53 UTC] gunther at ultraconsulting dot com

The problem is still open. If there is any help from my side required ... please let me know. If this problem persists I most probably would have to delete the DB layer and use direct MS-SQL statements. The SQL test scenario can be established by using Microsoft's MSDE (SQL server desktop edition) which is freely available.

[2004-04-09 04:16 UTC] gunther at ultraconsulting dot com

Would very much like to help out. But as you mentioned ... it's quite a complex problem. And it seems I am the only one who experienced this problem. When I do the same transactions w/o the DB layer, it works as intended. But I am not quite sure what the proper procedure for 'Sequence numbers' (nextId) is. What should be the correct sequence when one process is getting a nextId, has not yet committed this transaction, another transaction is doing the same thing and is successful, while the first transaction failed.
I presume the second transaction has to wait until the first one is completed ... otherwise the result would be out of sequence.
And which one is now the DB layer to use? DB, DO, MDB, MDB2 or ...

[2004-05-13 22:58 UTC] dave at mausner dot us

if the nextid process under mssql is based on obtaining the current id value without an intention-lock, then under stress several transactions will attempt to use the same nextid. the weird timing could be due to delay of posting the transaction until the last possible moment. an alternative would be to create a one-row next-id table for each data-table, then obtain the nextid by using an update+select of the next-id as part of the transaction, thus locking the sequence generation. HTH.

[2004-05-15 02:34 UTC] gunther at ultraconsulting dot com

Thanks Dave!
I guess I have to dig in and see how I can make it work. Or scrap PEAR DB for this part of the program as it seems to work with 'raw' mssql calls.

[2007-01-12 07:29 UTC] gunther at ultraconsulting dot com

Thanks for the update Adam! Yes, it was quite long ... but at least it was fixed. Thanks to you! I will try to test it in the very near future. In the mean time I build a workaround for it and that worked more or less for the past few years.
Gunther