Solved

TADOConnection.Begintrans and post

Posted on 2002-03-07
12
2,141 Views
Last Modified: 2011-10-03
Hi, I'm trying to use a POST with Begintrans but it lock the table until I call Commit or RollBack.
This is my code :

ADOConnection.Begintrans;
ADOQuery.Insert;
ADOQuery.FieldByName('MyField').AsString := 'STRING';
ADOQuery.Post
...[do somethings here]
ADOConnection.CommitTrans;

So why this lock the table while execute [do somethings here] ? How could I lock only the record and not the table ? Is there something to do to recover updates ( post ) after a CommitTrans ?
Thanks a lot.


0
Comment
Question by:mvsilva
  • 3
  • 3
  • 2
  • +4
12 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6849763
your database is ...?
0
 
LVL 2

Expert Comment

by:DelphiArchitect
ID: 6849783
Locking is pretty much a function of your Database server, not ADO.  Some database servers implement very simple (ie: inefficient) locking, although few use table locks these days.  

The better RDBMS servers give you some options for tuning lock performance.  You may want to check into this.  But without knowing what type of server you are using it isn't possible to suggest much else...

Delphi Architect
James Higgins
0
 
LVL 17

Expert Comment

by:geobul
ID: 6849917
Hi,
In addition to the previous questions I'd like to know what the values of the following properties are:
ADOConnection- IsolationLevel
ADOQuery- CursorLocation, CursorType, LockType, MarshalOptions.

What does the query SQL statement look like? What is [do somethings here]?

Regards, Geo
0
 

Author Comment

by:mvsilva
ID: 6850110
Sorry!
I'm using SQL Server 2000 and Delphi 6.
About isolation level I have tried some values. But now I'm using ilReadUncommitted.
LockType = ltBatchOptimistic;
MarshalOptions = moMarshalAll;
CursorType = ctKeyset;
CursorLocation = clUseClient;
Abaout [do somethings here] : I start a new transaction and get the next code avaible in the table and save it with post ( without committrans ). So I wait for the user to fill the next fields and then I save all ( [do somethings here] ). If the user press cancel button I call rollbackTrans else I call CommitTrans. While I wait for the user to type other fields the table stay locked and other clients can not access it until I call CommitTrans or RollBackTrans.
Thanks a lot.



0
 
LVL 17

Assisted Solution

by:geobul
geobul earned 100 total points
ID: 6850817
Do it this way:
1. User enters all the fields.
2.1. If he/she presses OK:
 - start a transaction
 - try
 - save all the data in the db
 - commit the trans
 - except rollback.
2.2.  else do nothing.
3. end.

The idea is to execute transactions at once. Do not wait for users while you are in a transaction.

Regards, Geo
0
 

Author Comment

by:mvsilva
ID: 6850847
Ok. I'm using it.
But this way I need to work with Grid offline cause If the user change some items on the grid the table will be locked until click ok ?! It´s bad !
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Accepted Solution

by:
DelphiArchitect earned 100 total points
ID: 6850973
When you say the table is locked, what have you tried?

MS SQL Server 2000 should not lock the entire table on you.  Previous versions used Page Locking and at some point they added Row Locking (can't remember which version, though, but 2000 should have Row Locking capability).

So if you start a transaction and then send in updates, inserts or deletes then other clients may be prevented from accessing the affected rows (even reading them).

When you say you "get the next code available and save it with post" what do you mean?  I'm assuming that this is what you are using for your primary key.  Is there any reason why you can't use an Identity column or such as the primary key instead?  My guess is that it is this part of your process that is getting "stuck" and locking the other clients.

Consider this:

Client A:
1. Starts a transaction
2. Does a select to find the MAX value of the key
3. Does an insert to create a mostly blank row that effectively just "locks" that key value

Client B:
1. Starts a transaction
2. Does a select to find the MAX value of the key
** could lock here
3. Does an insert to create a mostly blank row that effectively just "locks" that key value

Ok, Client B could hang at step #2 because there is a pending row that is part of a transaction.  The following are possible:

a. Client B hangs waiting for the As transaction to close
b. Client B does not see the new row inserted by Client A (read committed is set)
c. Client B does see the new row inserted by Client A (dirty read is set)

If a or b occur, your going to have a lock.  If b occurs it when then attempt to insert a record with the same primary key inside its own transaction.  I'm not certain what SQL server would do here either since it has 2 potential rows with the same key.

Basically, this path leads to a lot of questions.  Plus, by using this method you have to allow for nulls in most/all of your columns.  I highly suggest that you use a different method to get your primary keys (either an identity column, unique (GUID) column or use a generator).  This way you don't have to put partial rows other there that aren't valid.  Because what happens when you go to do reports or other such things and these partial rows exist out there?  This way you can also set required columns to not allow nulls.

If you can't use an identity column or unique (GUID) column then lets talk about a generator.  A simple generator is basically another table with records that represent individual generators.  Each row has a unique name (or whatever you want to use to identify them) and the next key value.  So you start a transaction, select the current max value, update the row to the next max value, and commit your transaction.  This gives you your primary key.  But since all this table does is give out keys there is no reason to require long transactions or have partial rows show up in your data tables.

Hope this helps.

Delphi Architect
James Higgins
0
 

Author Comment

by:mvsilva
ID: 6851140
Ok. I'm using it.
But this way I need to work with Grid offline cause If the user change some items on the grid the table will be locked until click ok ?! It´s bad !
0
 

Expert Comment

by:CleanupPing
ID: 9343296
mvsilva:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 17

Expert Comment

by:geobul
ID: 9346843
Split points among all experts.
0
 
LVL 7

Expert Comment

by:knightmad
ID: 9470493
Wow, Its been a long time since this comment have been visited!

mvsilva,
No comment has been added lately (19 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: split points between kretzschmar http:#6849763 and DelphiArchitect http:#6849783 and geobul http:#6849917

-- Please DO NOT accept this comment as an answer ! --

Thanks,

knightmad
EE Cleanup Volunteer

Remember you (all) can object this recommendation if you disagree, just leave a post here explaining why are you objecting and what should be done instead.
Within 7 days probably this thread will be closed, so, hurry up! : )
0
 

Expert Comment

by:YensidMod
ID: 9536736
Split between Delphiarchitect and geobul.  Kretzchmar only asked a question, not provide an answer.

Yensidmod
EE Moderator
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now