Hi all,
I have a strange problem with transactions and deletes with foreign keys.
I have these tow tables: Table A)
Code numeric(10) primary key,
description varchar(20)
values:
1, 'example table A 1'
2, 'example table A 2'
4, 'example table A 4'
10, 'example table A10'
Table B)
code numeric(10) primary key,
description varchar,
fk_tableA numeric(10) (Foreing key to table A)
values:
1, 'example table B 1', 1
2, 'example table B 2', 2
Let's see these cases:
Case 1) Begin transaction 1 isolation level uncommitted
I insert a record in table B: 3, 'example table B3', 1
Begin transaction 2 isolation level uncommitted
delete a record in table B: delete from B where code = 2
commit transaction 2 OK
commit transaction 1 OK
Case 2) Begin transaction 1 isolation level uncommitted
I insert a record in table B: 3, 'example table B3', 1
Begin transaction 2 isolation level uncommitted
delete a record in table A: delete from A where code = 4
commit transaction 2 Waits until transaction 1 ends???
commit transaction 1 OK and then transaction 2 ends????
Case 3) Begin transaction 1 isolation level uncommitted
I insert a record in table B: 3, 'example table B3', 1
Begin transaction 2 isolation level uncommitted
delete a record in table A: delete from A where code = 2
commit transaction 2 Waits until transaction 1 ends??? and then give's an
error becouse the integrity
commit transaction 1 OK and then transaction 2 ends????
Case 4) Begin transaction 1 isolation level uncommitted
I insert a record in table B: 3, 'example table B3', 1
Begin transaction 2 isolation level uncommitted
update a record in table A: update A set description = 'fooo' where code = 2
commit transaction 2 OK
commit transaction 1 OK
Case 5) Begin transaction 1 isolation level uncommitted
I insert a record in table B: 3, 'example table B3', 1
Begin transaction 2 isolation level uncommitted
delete a record in table B: delete from B where code = 2
commit transaction 2 OK
commit transaction 1 OK
Case 6) Begin transaction 1 isolation level uncommitted
I update the PK of a record in table B: update A set code = 11 where code = 10
Begin transaction 2 isolation level uncommitted
delete a record in table B: delete from A where code = 2
commit transaction 2 error because of the integrity but it doesn't wait
commit transaction 1 OK
In case 2 and 3 I don't understand why it wait until transaction 1 ends. This is how
maxdb work with deletes, if a transaction inserts a record in table B and another
transaction at the some time tries to delete a record in table A, because table B has
a FK with table A waits until the first transaction ends even the tow transaction
touch different records??? If this is the case way case 6 doesn't wait if I change the
pk value???.
We are using sapdb 7.4.3.30 and 7.5.0.8. we have tries to change the maxlocks
parameter because we think that tries to lock the entire table but nothing...
Is there a parameter we have to touch and the case 2 and 3 will works, is this a bug
or is how sapdb and maxdb works???
Thanks in advice, Oscar
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
