Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


TADOConnection.Begintrans and post

Posted on 2002-03-07
Medium Priority
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 :

ADOQuery.FieldByName('MyField').AsString := 'STRING';
...[do somethings here]

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.

Question by:mvsilva
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +4
LVL 27

Expert Comment

ID: 6849763
your database is ...?

Expert Comment

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
LVL 17

Expert Comment

ID: 6849917
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 6850110
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.

LVL 17

Assisted Solution

geobul earned 400 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

Author Comment

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 !

Accepted Solution

DelphiArchitect earned 400 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

Author Comment

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 !

Expert Comment

ID: 9343296
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 
Post your closing recommendations!  No comment means you don't care.
LVL 17

Expert Comment

ID: 9346843
Split points among all experts.

Expert Comment

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

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 ! --


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! : )

Expert Comment

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

EE Moderator

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 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