VB5 / ADO 1.5 / SQL Server 6.5
| Author |
Message |
|
Kevin Moor
#1 / 7
|
 VB5 / ADO 1.5 / SQL Server 6.5 Hello all,
I am having the following problem:
two different exes on two different machines try and lock the same record
within a transaction. The first user should be able to lock it and make
their changes at will. The second user should get a "unable to lock record
because it is currently locked by someone else" type of message.
but what happens is that the first user locks the record but when the second
user goes to lock the same record a deadlock occurs. if the first user tries
to save their changes they get a deadlock message from SQL Server and their
process is terminated. The second user can then continue and make changes.
I am using VB 5 EE, ADO 1.5c release and SQL Server 6.5. Does anyone have
any ideas what I am doing wrong.
Thanks,
Kevin Moore
|
| Fri, 22 Dec 2000 03:00:00 GMT |
|
 |
|
Thomas Kroje
#2 / 7
|
VB5 / ADO 1.5 / SQL Server 6.5 Hello Kevin, Im not shure at all, but SQL-Servr 6.5 CANNOT lock a single record, so the
complete page is locked ... This is _not_ a bug, its a feature
Maybe this is the source of your problem?
By, Thomas
Kevin Moore schrieb in Nachricht ...
Quote: >Hello all, >I am having the following problem:
>two different exes on two different machines try and lock the same record
>within a transaction. The first user should be able to lock it and make
>their changes at will. The second user should get a "unable to lock record
>because it is currently locked by someone else" type of message.
>but what happens is that the first user locks the record but when the
second
>user goes to lock the same record a deadlock occurs. if the first user
tries
>to save their changes they get a deadlock message from SQL Server and their
>process is terminated. The second user can then continue and make changes.
>I am using VB 5 EE, ADO 1.5c release and SQL Server 6.5. Does anyone have
>any ideas what I am doing wrong.
>Thanks,
>Kevin Moore
|
| Sat, 23 Dec 2000 03:00:00 GMT |
|
|
|
Kevin Moor
#3 / 7
|
VB5 / ADO 1.5 / SQL Server 6.5 Thomas, SQL Server suports page level locking, I think that is what you are
referring to. A record could "theoretically" span multiple pages or there
could be several records on a page, it all depends on the size of the page
and record. The problem I am having deals with the fact that the second
user appears to be blocking the first and I don't know why the wouldn't be
attempting to retrieve the same information?
Thanks
Kevin Moore
Quote:
>Hello Kevin, >Im not shure at all, but SQL-Servr 6.5 CANNOT lock a single record, so the
>complete page is locked ... This is _not_ a bug, its a feature
>Maybe this is the source of your problem?
>By, Thomas
>Kevin Moore schrieb in Nachricht ...
>>Hello all,
>>I am having the following problem:
>>two different exes on two different machines try and lock the same record
>>within a transaction. The first user should be able to lock it and make
>>their changes at will. The second user should get a "unable to lock record
>>because it is currently locked by someone else" type of message.
>>but what happens is that the first user locks the record but when the
>second
>>user goes to lock the same record a deadlock occurs. if the first user
>tries
>>to save their changes they get a deadlock message from SQL Server and
their
>>process is terminated. The second user can then continue and make changes.
>>I am using VB 5 EE, ADO 1.5c release and SQL Server 6.5. Does anyone have
>>any ideas what I am doing wrong.
>>Thanks,
>>Kevin Moore
|
| Sat, 23 Dec 2000 03:00:00 GMT |
|
|
|
Robi
#4 / 7
|
VB5 / ADO 1.5 / SQL Server 6.5 If the first user locked record A,B,the second user locked B.
Now if second user want to lock A,the deadlock occurs.
I don't know how your code are.Can you put it on?
|
| Sun, 24 Dec 2000 03:00:00 GMT |
|
|
|
Thomas Kroje
#5 / 7
|
VB5 / ADO 1.5 / SQL Server 6.5 Kevin, Maybe I didnt understand your question complete ....
In the Select statement, like "select * from tablename where
you can add something like this:
select * from tablename (tablock nolock) where ....
also you can add the index you want to use (sometimes this speed up the
select)
select * from tablename (tablock nolock index=Indexname) where ....
I use this in a app with about 200 users, and at the moment I dont get any
deadlocks ..
Bye, Thomas.
Kevin Moore schrieb in Nachricht ...
Quote: >Thomas, >SQL Server suports page level locking, I think that is what you are
>referring to. A record could "theoretically" span multiple pages or there
>could be several records on a page, it all depends on the size of the page
>and record. The problem I am having deals with the fact that the second
>user appears to be blocking the first and I don't know why the wouldn't be
>attempting to retrieve the same information?
>Thanks
>Kevin Moore
>>Hello Kevin,
>>Im not shure at all, but SQL-Servr 6.5 CANNOT lock a single record, so
the
>>complete page is locked ... This is _not_ a bug, its a feature
>>Maybe this is the source of your problem?
>>By, Thomas
>>Kevin Moore schrieb in Nachricht ...
>>>Hello all,
>>>I am having the following problem:
>>>two different exes on two different machines try and lock the same record
>>>within a transaction. The first user should be able to lock it and make
>>>their changes at will. The second user should get a "unable to lock
record
>>>because it is currently locked by someone else" type of message.
>>>but what happens is that the first user locks the record but when the
>>second
>>>user goes to lock the same record a deadlock occurs. if the first user
>>tries
>>>to save their changes they get a deadlock message from SQL Server and
>their
>>>process is terminated. The second user can then continue and make
changes.
>>>I am using VB 5 EE, ADO 1.5c release and SQL Server 6.5. Does anyone have
>>>any ideas what I am doing wrong.
>>>Thanks,
>>>Kevin Moore
|
| Sun, 24 Dec 2000 03:00:00 GMT |
|
|
|
Kevin Moor
#6 / 7
|
VB5 / ADO 1.5 / SQL Server 6.5 A code snippet is as follows: sSQL = "SELECT * FROM tblContractor WHERE ContractorCode = 'T001'"
set mrs = new adodb.recordset
mrs.open ssql, cnADO, adoOpenDynamic, adLockPessimistic
On the above statement a deadlock occurs if two different users on different
machines execute the same line of code.
Kevin
Quote:
>If the first user locked record A,B,the second user locked B.
>Now if second user want to lock A,the deadlock occurs.
>I don't know how your code are.Can you put it on?
|
| Sun, 24 Dec 2000 03:00:00 GMT |
|
|
|
Kevin Moor
#7 / 7
|
VB5 / ADO 1.5 / SQL Server 6.5 Thomas, Thanks for the tip, I usually do use the syntax you mentioned in the select
to perform locking and for index usage. But this time I was attempting to
use the ADO locking mechansim to lock a record, due to the fact that I might
want to port my app from SQL server back to Access for some clients.
Kevin
Quote:
>Kevin, >Maybe I didnt understand your question complete ....
>In the Select statement, like "select * from tablename where
>you can add something like this:
>select * from tablename (tablock nolock) where ....
>also you can add the index you want to use (sometimes this speed up the
>select)
>select * from tablename (tablock nolock index=Indexname) where ....
>I use this in a app with about 200 users, and at the moment I dont get any
>deadlocks ..
>Bye, Thomas.
>Kevin Moore schrieb in Nachricht ...
>>Thomas,
>>SQL Server suports page level locking, I think that is what you are
>>referring to. A record could "theoretically" span multiple pages or there
>>could be several records on a page, it all depends on the size of the page
>>and record. The problem I am having deals with the fact that the second
>>user appears to be blocking the first and I don't know why the wouldn't be
>>attempting to retrieve the same information?
>>Thanks
>>Kevin Moore
>>>Hello Kevin,
>>>Im not shure at all, but SQL-Servr 6.5 CANNOT lock a single record, so
>the
>>>complete page is locked ... This is _not_ a bug, its a feature
>>>Maybe this is the source of your problem?
>>>By, Thomas
>>>Kevin Moore schrieb in Nachricht ...
>>>>Hello all,
>>>>I am having the following problem:
>>>>two different exes on two different machines try and lock the same
record
>>>>within a transaction. The first user should be able to lock it and make
>>>>their changes at will. The second user should get a "unable to lock
>record
>>>>because it is currently locked by someone else" type of message.
>>>>but what happens is that the first user locks the record but when the
>>>second
>>>>user goes to lock the same record a deadlock occurs. if the first user
>>>tries
>>>>to save their changes they get a deadlock message from SQL Server and
>>their
>>>>process is terminated. The second user can then continue and make
>changes.
>>>>I am using VB 5 EE, ADO 1.5c release and SQL Server 6.5. Does anyone
have
>>>>any ideas what I am doing wrong.
>>>>Thanks,
>>>>Kevin Moore
|
| Sun, 24 Dec 2000 03:00:00 GMT |
|
| |
|
