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 |
|
devteam
Starting Member
15 Posts |
 Posted - 2006-02-20 : 07:29:23
|
| Hi all,I am using sql server 2000. while running my application, i will execute simple select statement. when multiple users accessed the same query then i did not retrieve the result.When i was executing the below given sql script to find out which transaction causes the lock then,SELECT spid, cmd, status,blockedFROM master..sysprocesses pWHERE open_tran > 0AND spid > 50AND datediff (s, last_batch, getdate ()) > 30ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)it retrieves the value as51 SELECT sleeping 5555 SELECT sleeping 6160 SELECT sleeping 5561 AWAITING COMMAND sleeping 0 62 SELECT sleeping 55What is meant by awaiting command and sleeping status.. why the problem is occured..can anyone clearly explain me... |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-20 : 12:17:09
|
| It looks like spid 61 is the root of a chain of blocks but it isn't doing anything (awaiting command) . 61 is not blocked, sleeping but is blocking 55. 55 is blocking 51, 60, and 62 which are all trying to complete Selects.That and the fact there are open transactions says to me that you have some poorly handled transaction control going on. Are you explicitly beginning/ committing / rolling back transactions from your application and/or from stored procedures? There are a lot of techiques for trouble shooting this type of thing, but one thing I'd do is see if the command for spid 61 is still in the buffer: dbcc inputbuffer(61)Also try running "dbcc opentran" to see the oldest open transaction.What is the basic architecture you are using for reading and writing to sql server? Are you sharing this database with other applications or direct user access?Be One with the OptimizerTG |
 |
|
|
devteam
Starting Member
15 Posts |
Posted - 2006-02-20 : 22:27:18
|
Hi,Thanks for ur information. i have used dbcc inputbuffer(spid) to check the which query is used.actually our database is used for only one application and also it have direct user access.why do we have any problem when direct user access?.what i have to do for that?Thanksquote:
Originally posted by TG
It looks like spid 61 is the root of a chain of blocks but it isn't doing anything (awaiting command) . 61 is not blocked, sleeping but is blocking 55. 55 is blocking 51, 60, and 62 which are all trying to complete Selects.That and the fact there are open transactions says to me that you have some poorly handled transaction control going on. Are you explicitly beginning/ committing / rolling back transactions from your application and/or from stored procedures? There are a lot of techiques for trouble shooting this type of thing, but one thing I'd do is see if the command for spid 61 is still in the buffer: dbcc inputbuffer(61)Also try running "dbcc opentran" to see the oldest open transaction.What is the basic architecture you are using for reading and writing to sql server? Are you sharing this database with other applications or direct user access?Be One with the OptimizerTG
|
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-20 : 23:16:13
|
| Direct user access can be bad because you have little control over what the user is doing. Imagine a user opening a table using an odbc dsn and MS Access. (I think) That could lock the entire table for as long as the table is open on there desktop. Besides performance issues it can also be bad for security reasons depending on what priviledges have been granted to the user(s).But based on your original statement:"when multiple users accessed the same query then i did not retrieve the result."sounds like your own application operations are blocking each other for extended periods of time. This can be because of not too optimized queries, improper use of transaction control, and inappropriate isolation levels. I Can't really talk specifically about problems you might be having without seeing actual code but hopefully you've got some more info to attack the problem yourself.Be One with the OptimizerTG |
|
|
|
devteam
Starting Member
15 Posts |
Posted - 2006-02-21 : 03:49:51
|
Hi,How to change the direct user access in our application?.we are using default isolation TRANSACTION_READ_COMMITTED.i did not understand transaction control., because for select query we are not going to use any transaction control like commit or rollback. we are just selecting the values.can u explain it? is this because of problem in the nested files?quote:
Originally posted by TG
Direct user access can be bad because you have little control over what the user is doing. Imagine a user opening a table using an odbc dsn and MS Access. (I think) That could lock the entire table for as long as the table is open on there desktop. Besides performance issues it can also be bad for security reasons depending on what priviledges have been granted to the user(s).But based on your original statement:"when multiple users accessed the same query then i did not retrieve the result."sounds like your own application operations are blocking each other for extended periods of time. This can be because of not too optimized queries, improper use of transaction control, and inappropriate isolation levels. I Can't really talk specifically about problems you might be having without seeing actual code but hopefully you've got some more info to attack the problem yourself.Be One with the OptimizerTG
|
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-21 : 08:09:24
|
| >>How to change the direct user access in our application?.I am referring to user's having direct access to the database (not through your application)Read up on "security accounts". Make sure all users use their own logins and deny direct access to tables. >>i did not understand transaction control., because for select query we are not going to use any transaction control like commit or rollbackSince the predicate in your query against sysProcesses limited to rows to "open_tran > 0" Someone somewhere is performing a BEGIN TRAN.The connection with the "open transaction" is blocking your select statements.>>we are using default isolation TRANSACTION_READ_COMMITTED.If you don't mind "dirty reads", one option is to perform your SELECTs using READ UNCOMMITTED isolation level.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Row level lock