| Author |
Topic |
|
ann06
Posting Yak Master
171 Posts |
 Posted - 2012-02-22 : 09:04:37
|
| hi,when i open a table in sql manager using select * from table 1iam unable to run update query for a record if the table is still open in enterprise manager until i close it will run.but, i tried for different table in different db iam able to update a record on a table while the table is open in Enterprise manager.can any body explain why in first case its is blocked while in second case its running? is this a configuration issue?thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-22 : 09:35:32
|
| is the select run under a transaction?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-22 : 10:04:19
|
| once you get locking process ID (if one exists) you can check what command that process is executing by using below commandDBCC inputbuffer(<ProcessID>------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2012-02-23 : 00:51:43
|
| -->visakh: no its not running under a transaction its running under right click/open table/return all rowssteps are-->X002548: 1. in ssms i right click the table mail_sent return all rows it run and displays the result (select * from mail_sent)2. in query analyzer i run any update command for any single record (update MAILER.DBO.MAIL_SENT set read_alert='0'where Sent_id='248248')3. the query will wait for 5 min and 40 sec. to complete5. while its waiting i checked the locks using the current activity : locks/process ID6.its giving one process 69 blocking blocking command is SELECT * FROM MAIL_SENT (lock type is TAB mode S)7. one process 55 blocked by 69 command is update MAILER.DBO.MAIL_SENT set read_alert='0'where Sent_id='248248' lock type TAB mode IXnote the table mail_sent has no of records = 245763 if i runt the query select * from QA it takes 40 sec to complete but not than 5 mins and morethanks |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2012-02-23 : 01:22:10
|
| Check your isolation level..Senthil Kumar C------------------------------------------------------MCITP - Database Administration SQL SERVER 2008MCTS - Database Development SQL SERVER 2008 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-23 : 09:02:53
|
| are you in MAILER DB or are there two different databases involved?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-23 : 09:24:23
|
If you use SSMS to open (Edit Top 200 Rows), you will be locking the rows in the Table view.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
why it is locking an update query ?