Sorry for the wordiness.

VC++ 8.0, XP Pro SP2, etc. We're porting from Borland C++
Builder/ADO to VC++/ODBC. This worked fine with BCB/ADO.

First, the rationale. We have a number of 'batch' (console) apps
that can not be run at the same time due to possible conflicts in
DB tables. The apps can be run on a number of different servers,
and they all access the same DB, so named semaphores were out.
We were using a DB table as a semaphore. The idea was to insert
a row in the table with the name of a table that should be
'logically' locked. The locked table name column name is unique.
We don't actually want to lock the table itself because other
read only programs should be able to use the table.

I'm sorry if that isn't crystal clear. Here's the problem. I
put together a test program in order to determine the problem.
Very simply,

- Set up the hEnv and hDbc stuff.
- Execute "SET LOCK_TIMEOUT 2000"
- Set SQL_AUTO_COMMIT_OFF
- Execute the INSERT statement
- Wait for keyboard input

Run the program. Fine. Run another instance and it 'hangs' on
the INSERT. Where's my timeout? The second instance won't "come
back" until the first instance terminates.

Thanks for any help,
- Arnie

Hello Arnie,

When Auto_commit is set to off, all executed statements are included in the
same transaction until it is specifically terminated by calling SQLEndTran.
It seems you wait keyboard during the transaction which is not recommended.
This will lock the record with exclusive lock and it will not release
before transaction is committed. You could run sp_lock and check
sysprocesses for details.

With lock timeout set, the blocked statement is canceled automatically, and
error message 1222 "Lock request time-out period exceeded" is returned to
the application.

However, any transaction containing the statement is not rolled back or
canceled by SQL Server. Therefore, the application must have an error
handler that can trap error message 1222. If an application does not trap
the error, it can proceed unaware that an individual statement within a
transaction has been canceled, and errors can occur because statements
later in the transaction may depend on the statement that was never
executed.

Please run sql profiler to captuer the errors/locks informaiton to see if
you could see the timeout message.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

Thanks for your reply.

Waiting for keyboard input is only done in the test program. It
is done so that I can run the second instance of the program to
see what happens when it tries to insert an identical record.

The problem is that the second instance 'hangs' on the SQLExecute
that attempts the insert. Therefore, there is no return code.
If I allow the first instance to complete, then the second
instance returns from the SQLExecute with, as expected, a unique
constraint violation.

My question is: Why doesn't the second instance time out? Does
the lock timeout value apply to all SQL statements or just to a
SELECT?

Thanks,
- Arnie

Hello Arnie,

Did you check the @error varialbe? You may want to use the steps in the
following KB to check if you could get the lock timeout error in Query
Analyzer

FIX: LOCK_TIMEOUT causes transaction to roll back and @@error does not
capture error 1222
http://support.microsoft.com/kb/q286286/

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------