Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-03-04 : 08:23:06
|
| Marc writes "Hi, I use VB as a front-end and my back-end database is SQL Server 2000. I read a lot about the locking. What I'm experiencing is the following: I run 2 applications (It is the same). One is the compiled version and the other I run directly from VB. The compiled version locks the record with Pessimistic locking (I know i could use Optimistic, but in this case, Pessimistic is the only one I need). When the program running through VB tries to just read the record, not even lock it, I get a timeout error. I've read that the transaction level could be the reason for this behavior. I've tried different things. But I'd like to find out more about that. Here is a sample of my connection being opened and the opening of the REcordset: Set ConnAssurmed = New ADODB.Connection ConnAssurmed.Open <DSN object>, <user name> 'Re-set the Recordset variable Set rsUsagers = New ADODB.Recordset strSQL = "Select * from usagers where upper(nomusager) = <username>" 'Open the Query into the Recordset rsUsagers.Open strSQL, ConnAssurmed, adOpenKeyset, adLockPessimistic, adCmdText <variable name> = <recordset field> <- Just doing this whent he compiled version has locked the record creates the timeout. I still have to read more stuff, but I'm really confused as to why this is happening. Could you please help me out? Thanks a lot.Marc" |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2004-03-04 : 12:31:51
|
| I think the transaction isolation level SQLOLEDB uses is READ COMMITTED, which means that if you (your compiled exe) is locking rows with pessimistic locking, but hasn't updated them yet and issued a COMMIT or ROLLBACK, you won't be able to read those rows even without updating them because they aren't committed yet. You are timing out because you are basically deadlocking yourself. A keyset cursor doesn't give you a copy of the rows like a static cursor does, it only contains a set of keys to the data, so that's probably why the timeout doesn't occur on the .Open line but rather when you are actually trying to access the data and the recordset is trying to fetch it from the database (and it's locked).To avoid this scenario, you can change the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED. To see what the current level is, check out Connection Dynamic Properties. You can loop all the dynamic properties in a loop to see their names and values, and you can change values.Sarah Berger MCSD |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-04 : 23:55:15
|
| And if you are only ever going to be selecting, and not updating, deleting or inserting, then use optimistic locking. It's never a good idea to use a locking or transaction isolation level that's higher than what you really NEED. |
|
|
|
|
|
|
|
|
VB & SQL Server Recordset timeout error