0

I have got some strange problem withe FireDAC and locking on mssql-server and have no idea how this can happen. So I hope anybody has some experience and will help me :)

We have got the following situation: In our application we have got a table ToDo and a table ToDoNotes. Both have primary unique keys ID and they are linked via ToDoNotes.ToDoID. Table ToDo is used to write down the next tasks and table ToDoNotes is to leave comments on each of the tasks. So several records in table ToDoNotes can be for one record in table ToDo. We connect to these mssql-tables using FireDac Table-Component FDTable. The connection has lockmode pessimistic with locktpoint immediate.

The following steps are locking the complete table ToDoNotes and I do not know why:

Session one:

  1. TblToDo.Edit for record 1
  2. TblToDoNotes.Append to append a record for TblToDo-record 1

so both Tables are in EditMode now.

Session two:

  1. TblToDoNotes.Append

At this point the application (Session two) is freezing until, we do a TblToDo.Post or a TblToDo.Cancel in Session one. In the Microsoft Managment Studio I can see session two with waittype LCK_M_S and resource ridlock

Has anyone an idea what is going wrong? If we append records to TblToDoNotes in two different sessions everything works fine. It seems to me that FireDAC creates a second transaction which blocks the whole table ToDoNotes if there is already one table in editmode or something like that.

Every help is greatly appreciated.

Markus

Maggi_MT
  • 71
  • 6

2 Answers2

0

I think your problem is that you create the master record and in the same time the detail record. At this time the master record has no valid ID, after you post it, it gets shurely a valid ID and you can use it in the detail record. From my experience the safe way is to create first the master record, post it and afterwards add the detail records

Christine Ross
  • 465
  • 5
  • 14
  • Thanks for your reply, but I guess there is a missunderstanding. I already have got 2 records in TblToDo and just set recrod 1 in EditMode by calling TblToDo.Edit. Than I start do add a record by TblToDoNotes.Append. At this point everything is still OK. Only when someone starts to add another record in TblToDoNotes by calling TblToDoNotes.Append his session freezes. – Maggi_MT Mar 12 '15 at 11:21
  • Yes, could be a misunderstanding. I don't understand why do you need to set a record in TblToDo in Edit mode, in order to add a Notes record (? is that what you mean?). Can you please show your design of the two tables and how do you join them. My gut feeling says that there could be the cause of the problem – Christine Ross Mar 12 '15 at 15:33
  • There is no need to to set a record in TblToDo in EditMode to append a record in TblToDoNotes. But that is actually what the users do and I want to understand how that could run in my described problem. It is easy to block the "TblToDoNotes.Append"-function if the user already has got a record of TblToDo in EditMode but first I want to understand why this makes such a big problem. TblToDo has got the fields Name (nchar, 40) and description (nvarchar(max)) and TblToDo has got the fields Memo (nvarchar(max)) and ToDoID (Integer). "ToDoID" links the two tables by a rule of referential Integrity. – Maggi_MT Mar 16 '15 at 13:13
0

Did you succeed with regards to this problem ? I have had a similar issue and it seems to be the fetchoptions mode paramater. When set to onDemand , it leaves the process open and waiting. I resolved my issue by setting this to fetchall, however, the downside is that the query takes longer to open.

Oliver
  • 83
  • 11