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.
| Author |
Topic |
|
Rochak
Starting Member
6 Posts |
 Posted - 2003-04-25 : 02:28:02
|
| Please help me, my situtation is I have a transaction that updates a Master-Detail tables, I begin with DataBase1.StartTransaction, then I insert a record inside a Master table but does not post it until the entire transaction is finish. The problem occurs when I start inserting a record in the Detail table and post it, because each record in the Master table can have many corresponding records on its Detail table. This causes the system to lock the entire Detail table until I commit or rollback the transaction, thus, preventing other session of my application to access other records in that table.I already check the SQL server and base on its documentation it supports various transaction level such as DirtyRead, RepeatableRead, Serializable, ReadCommited, and also tried to set the property "TransIsolation" of my Database1 component to different values like ReadCommited, DirtyRead, RepeatbleRead but there is no effect. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-25 : 02:41:57
|
| You've used the phrase Database1.StartTransaction. This is not a SQL command, but rather appears to be a method to have ADO control the transaction (I'm taking a wild stab that this is written in .Net... am I close?) My suggestion would be to pass this work off to SQL Server, such as in a Stored Procedure, and use the SQL transaction control commands rather than have ADO control it.And why do you need the Parent insert to be part of the same transaction as all the children? If you do this, then if one child fails, the whole group fails. Is that what you really want? How many child records will you be inserting initially?------------------------------------------------------The more you know, the more you know you don't know. |
 |
|
|
Rochak
Starting Member
6 Posts |
Posted - 2003-04-25 : 02:49:59
|
| The code is written using DELPHI.Does the number of child rows make a difference? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-25 : 03:08:31
|
| Ahh... I'm not familiar with DELPHI other than having heard the name a few times.No, the number of child records shouldn't dictate how the technology works, that was more of a "consultant question" probing for info that might lead me to a more elegant solution.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-25 : 04:30:54
|
| Why not insert all master and child records into temp tables then do a single SP cal to insert into the master/child tables. The SP can then control the tranaction and the insert will be 2 statements (one insert for each table) and very quick.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Table locks until posted transaction is committed.