TADOConnection.Begintrans and post

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

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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