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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.


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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Suggested Solutions

Title # Comments Views Activity
Delphi XE10, DigitalPersona Fingerprint reader and MySQL 6 335
Printing problem 2 102
Dynamically Created Query 3 62
FMX TEdit KeyUp handler detecting  "enter" key 4 14
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 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