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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
proper way to parse text with delphi 7 121
Working with hours 3 57
how to update exe applicatio from internet ? 6 77
Twebbrowser in Fire monkey android scrolling to bottom 1 30
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…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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