Oscar Radio wrote : >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??? Whenever you delete a row from a referenced table (table A in your example) MaxDB requires a temporary table share lock for the referenced tables (table B). This is why you have to wait in case 2 and 3 because of the inserts into table B. The reason for the temporary table share lock is the delete behavior of MaxDB. Without this lock a delete of a row from table B which is rolled back may cause a referential constraint violation. There is no way to change this behavior via any parameter. Best Regards Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
