|
|
You're in the
sql server mseq
group:cannot insert(urgent)
sql server mseq:
I am using Mssql2000 and i am facing a problem while saving data from my application. This Procedure is working fine. CREATE PROCEDURE test(@edate datetime,@supp_idv char (8),@div char(2),@dept char(3)) AS CREATE table #oi(supp_id char(8),inv_no char (15),inv_date datetime NULL,dept_code char(3),dr numeric(14,3),cr numeric (14,3),bal numeric(14,3),tr_type char(2),entry_type char(1)) begin Insert into #oi select supp_id,inv_no,inv_date,dept_code,dr,cr, (select sum(cr) - sum(dr) from ap_spled b WHERE a.supp_id = b.supp_id and a.inv_no = b.inv_no group by b.inv_no),trans_type,entry_type from ap_spled a where division = @div AND inv_date <= @edate and supp_id = @supp_idv and (select sum(cr) - sum(dr) from ap_spled b WHERE a.supp_id = b.supp_id and a.inv_no = b.inv_no group by b.inv_no) < 0 ; select a.supp_id,a.inv_no,a.inv_date,a.dr,a.cr,a.bal,a.tr _type,a.entry_type,a.dept,d.name from #oi a,supplier_file d where a.supp_id = d.supplier_id order by supp_id,inv_no The problem is that after retrieving this report in my application,if any other user try to save from any other module ie inserting any record into ap_spled table, the application is hanging and cannot insert into this table.No users can save their data this time, it is creating big prblm.This is the insert from my appl.This syntax is also working fine if the procedure is not execute. INSERT INTO ap_spled (tr_no,entry_date,supp_id,dr_amt,cr_amt,trans_type,acc_desc,division,dept_code,entry_type,inv_no,inv_date ) VALUES (:tr,:dt,:sup,:dmt,:amt,'uu',:descr,:division,:dept_code,:entry_type,:no,:vd ) ; Is their any problem in my storedproced syntax or this is due to any table locking pblm.How can i solve this.This is creating too much prbm Please help. Thanks --
This is the query analyser result after giving sp_who2 at the time of same
prblm. And If I Close the application that generated the report, the hanging will release and can save the data. SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID ----- ------------------------------ ----- -------- ----- ------- ---------------- ------- ------ -------------- ------------------ ----- 1 sleeping sa . . NULL LOG WRITER 0 0 03/12 11:57:30 1 2 BACKGROUND sa . . master SIGNAL HANDLER 0 0 03/12 11:57:30 2 3 BACKGROUND sa . . NULL LOCK MONITOR 0 0 03/12 11:57:30 3 4 BACKGROUND sa . . NULL LAZY WRITER 0 0 03/12 11:57:30 4 5 BACKGROUND sa . . master TASK MANAGER 0 17 03/12 11:57:30 5 6 BACKGROUND sa . . master TASK MANAGER 0 0 03/12 11:57:30 6 7 sleeping sa . . NULL CHECKPOINT SLEEP 0 51 03/12 11:57:30 7 8 BACKGROUND sa . . master TASK MANAGER 0 0 03/12 11:57:30 8 9 BACKGROUND sa . . master TASK MANAGER 0 9 03/12 11:57:30 9 10 BACKGROUND sa . . master TASK MANAGER 0 0 03/12 11:57:30 10 11 BACKGROUND sa . . master TASK MANAGER 0 0 03/12 11:57:30 11 12 BACKGROUND sa . . master TASK MANAGER 0 0 03/12 11:57:30 12 13 BACKGROUND sa . . master TASK MANAGER 0 0 03/12 11:57:30 13 14 BACKGROUND sa . . master TASK MANAGER 0 647 03/12 11:57:30 14 51 sleeping sa . . sns_acc AWAITING COMMAND 5672 24 03/13 09:55:02 51 52 sleeping sa . . sns_acc AWAITING COMMAND 2859 0 03/13 09:59:10 52 53 sleeping sa . . sns_acc AWAITING COMMAND 2734 0 03/13 09:58:20 53 54 sleeping sa . . sns_acc AWAITING COMMAND 11750 20 03/13 09:36:53 54 55 sleeping sa . . sns_acc AWAITING COMMAND 4594 0 03/13 09:42:13 55 56 sleeping sa . . sns_acc AWAITING COMMAND 2547 1 03/13 09:54:14 56 57 sleeping sa . . sns_acc AWAITING COMMAND 4594 194 03/13 09:50:06 57 58 sleeping sa . . sns_acc AWAITING COMMAND 5500 0 03/13 09:58:07 58 59 sleeping sa . . sns_acc AWAITING COMMAND 5266 0 03/13 09:14:18 59 60 sleeping sa . . sns_acc AWAITING COMMAND 8875 0 03/13 09:13:22 60 61 sleeping sa . . sns_acc AWAITING COMMAND 9000 0 03/13 09:15:11 61 62 sleeping sa . . sns_acc AWAITING COMMAND 4594 0 03/13 09:42:33 62 63 sleeping sa NAZAR . sns_acc AWAITING COMMAND 5344 0 03/13 09:55:02 MS SQLEM 63 64 sleeping sa . 66 sns_acc SELECT 3031 0 03/13 10:00:11 64 65 RUNNABLE sa NAZAR . sns_acc SELECT INTO 4594 11 03/13 09:49:33 SQL Query Analyzer 65 66 sleeping sa . 58 sns_acc INSERT 5719 0 03/13 09:59:55 66 67 sleeping sa . . sns_acc AWAITING COMMAND 5297 0 03/13 09:52:47 67 [quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 11 Mar 2005 23:13:02 -0800, shif wrote: > > (snip) > >The problem is that after retrieving this report in my application,if any > >other user try to save from any other module ie > >inserting any record into ap_spled table, the application is > >hanging and cannot insert into this table. > (snip) > > Hi shif, > > Some follow-up questions, the answers to which might help pinpoint the > poblem: > > 1. When this happens, how do you solve the issue? Wait until it goes > away (and if so, how long does that take, on average)? Close and restart > the application that tries to insert? Close and restart the application > that generated the report? Shutdown and restart the server? Or yet > something else? > > 2. Next time this happens, BEFORE taking any corrective action, open a > Query Analyzer window; type EXEC sp_who2; EXEC sp_lock and execute (make > sure that the "Results in Text" option is chosen), then copy the output > and paste it in a reply to this message. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
[quoted text, click to view]
On Fri, 11 Mar 2005 23:13:02 -0800, shif wrote: (snip) [quoted text, click to view] >The problem is that after retrieving this report in my application,if any (snip)>other user try to save from any other module ie >inserting any record into ap_spled table, the application is >hanging and cannot insert into this table. Hi shif, Some follow-up questions, the answers to which might help pinpoint the poblem: 1. When this happens, how do you solve the issue? Wait until it goes away (and if so, how long does that take, on average)? Close and restart the application that tries to insert? Close and restart the application that generated the report? Shutdown and restart the server? Or yet something else? 2. Next time this happens, BEFORE taking any corrective action, open a Query Analyzer window; type EXEC sp_who2; EXEC sp_lock and execute (make sure that the "Results in Text" option is chosen), then copy the output and paste it in a reply to this message. Best, Hugo --
Hi sir
I forget the sp_lock i am adding here and it is lengthy upto 450 rows i deleted some rows.if i have your mail id i can mail you the full text file. One more thing i forgot to tell is ,actualy the database which i am using was working in mssql7.I was just attached to ms2000 srvr,now i exported all data into a new database in same server,But again i saw today again from same screen not save but hanging one time in new db.here is the sp_who2 and sp_lock .. sp_lock spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 51 2 3 2 KEY (9c01d388586f) X GRANT 51 2 2 1 KEY (590093cc6231) X GRANT 52 8 0 0 DB S GRANT 53 8 0 0 DB S GRANT 54 8 0 0 DB S GRANT 55 8 0 0 DB S GRANT 56 8 0 0 DB S GRANT 56 2 6 0 TAB IX GRANT 56 2 2 0 TAB IX GRANT 56 2 1 0 TAB IX GRANT 56 2 3 1 KEY (7d00de4e4040) X GRANT 56 2 3 1 KEY (8000885e2318) X GRANT 56 8 1410104064 0 TAB S GRANT 56 2 3 1 KEY (810066f1960a) X GRANT 56 2 3 2 KEY (120262fbbe82) X GRANT 56 2 3 2 KEY (b901f41a78b7) X GRANT 56 2 3 2 KEY (a30233242999) X GRANT 56 2 3 2 KEY (a701bbd2b59b) X GRANT 56 2 3 1 KEY (820003962ab2) X GRANT 56 2 0 0 IDX IDX: 2:100335883 X GRANT 56 2 1003358839 0 TAB Sch-M GRANT 56 2 3 1 KEY (7e00bb29fcf8) X GRANT 56 2 3 2 KEY (c401f8c9de07) X GRANT 56 2 3 2 KEY (ad024e966d4d) X GRANT 57 2 1 2 KEY (b516cfe74d67) X GRANT 57 2 3 1 KEY (ef00eaadae35) X GRANT 57 2 3 2 KEY (9c020770e7da) X GRANT 57 2 2 1 KEY (e80092c5f44b) X GRANT 57 2 0 0 IDX IDX: 2:891358440 X GRANT 57 2 891358440 0 TAB Sch-M GRANT 57 2 3 2 KEY (a602bbabc534) X GRANT 57 2 3 1 KEY (ed006165a79f) X GRANT 57 2 3 1 KEY (f100d9da71d5) X GRANT 57 2 3 2 KEY (9f0226e43aa2) X GRANT 57 2 3 2 KEY (0e02b8060e3b) X GRANT 57 2 1 3 KEY (e800e561205a) X GRANT 57 2 3 1 KEY (f000bcbdcd6d) X GRANT 57 2 3 1 KEY (ec0004021b27) X GRANT 57 2 3 1 KEY (ee008fca128d) X GRANT 57 2 3 1 KEY (f2003775c4c7) X GRANT 57 2 3 2 KEY (9602c76d7c54) X GRANT 57 2 99 0 RID 1:78:1 X GRANT 57 2 3 1 KEY (ea00d85d7002) X GRANT 57 2 3 2 KEY (12035d1d7fdb) X GRANT 57 2 3 2 KEY (2b02dc4b8826) X GRANT 57 2 3 2 KEY (1e033df41ec8) X GRANT 57 2 3 1 KEY (eb00bd3accba) X GRANT 57 2 1 1 KEY (e8009061461e) X GRANT 57 2 3 2 KEY (35023d7f78d0) X GRANT 57 2 0 0 PAG 1:88 X GRANT 57 2 9 0 TAB IX GRANT 57 2 12 0 TAB IX GRANT 57 2 11 0 TAB IX GRANT 57 2 99 0 RID 1:78:0 X GRANT 57 2 0 0 PAG 1:78 X GRANT 57 2 0 0 PAG 1:77 X GRANT 57 2 0 0 EXT 1:88 X GRANT 58 2 3 1 KEY (89008d396180) X GRANT 58 2 3 2 KEY (5e01e45197c4) X GRANT 58 2 3 1 KEY (85003586b7ca) X GRANT 58 2 3 2 KEY (e601a49ec50f) X GRANT 58 2 3 1 KEY (35005b77329d) X GRANT 58 2 99 0 RID 1:121:0 X GRANT 58 2 99 0 RID 1:123:0 X GRANT 58 2 3 1 KEY (3100e3c8e4d7) X GRANT 58 2 0 0 PAG 1:121 X GRANT 58 2 3 1 KEY (4d00badd2f69) X GRANT 58 2 2 1 KEY (49007dcee1a2) X GRANT 58 2 99 0 RID 1:252:0 X GRANT 58 2 0 0 PAG 1:252 X GRANT 58 2 3 1 KEY (4c0003e5f8f4) X GRANT 58 2 3 1 KEY (4f00311526c3) X GRANT 58 2 3 1 KEY (4e00dfba93d1) X GRANT 58 2 3 1 KEY (530089aaf089) X GRANT 58 2 3 1 KEY (52006705459b) X GRANT 58 2 3 1 KEY (3a0048a3eb2f) X GRANT 58 2 3 2 KEY (000231e2855c) X GRANT 58 2 3 2 KEY (6f01f7b5e64f) X GRANT 58 2 3 2 KEY (57026d5bbde3) X GRANT 58 2 3 2 KEY (7a01dcdc9a6a) X GRANT 58 2 3 2 KEY (5b0251f72f39) X GRANT 58 2 3 1 KEY (3600f01c3d65) X GRANT 58 2 3 2 KEY (3602a72cacc0) X GRANT 58 2 622833481 0 TAB Sch-M GRANT 58 2 494833025 0 TAB Sch-M GRANT 58 2 1 3 KEY (4900a5f7bb28) X GRANT 58 2 3 1 KEY (51000262f923) X GRANT 58 2 3 2 KEY (590270009824) X GRANT 58 2 3 2 KEY (b001ef43c30a) X GRANT
[quoted text, click to view]
On Sat, 12 Mar 2005 22:23:01 -0800, shif wrote: (snip)>This is the query analyser result after giving sp_who2 at the time of same >prblm. >And If I Close the application that generated the report, >the hanging will release and can save the data. Hi shif, Thanks for posting this. Have a look at these three rows: [quoted text, click to view] >SPID Status Login HostName BlkBy DBName Command > CPUTime DiskIO LastBatch ProgramName SPID >----- ------------------------------ ----- -------- ----- ------- >---------------- ------- ------ -------------- ------------------ ----- (snip) >58 sleeping sa . . sns_acc AWAITING >COMMAND 5500 0 03/13 09:58:07 58 (...) >64 sleeping sa . 66 sns_acc SELECT > 3031 0 03/13 10:00:11 64 (...) >66 sleeping sa . 58 sns_acc INSERT > 5719 0 03/13 09:59:55 66 As you can see (even easier if you edit them to merge the split lines again), the connection with SPID 64 is blocked by (column BlkBy) SPID 66, which is in turn blocked by connection 58. Unfortunately, you didn't post the output from sp_lock. But I guess that this would show that either SPID 66 or (more likely) SPID 58 is used for generating the report, but still holds some locks that are also required for the insert. My guess, at this point, would be that the code that generated the report opens a transaction, but fails to close it. In the transaction, either some data gets updated, or a non-standard isolation level is used to maintain locks on data being read. While the acquiring of the locks might be legitimate in the context of the report generation, they should really be released when the report is finished. My suspicion is that this doesn't happen, because the report generating code fails to execute an COMMIT TRANSACTION command. When you close the application, the connection gets broken and SQL Server will rollback the transaction and then release the locks. Then, your other connection will get the locks they required and were wating for; they'll continue to run - until you re-run the report. Best, Hugo --
[quoted text, click to view]
On Sun, 13 Mar 2005 23:19:02 -0800, shif wrote: >Hi sir >I forget the sp_lock i am adding here and it is lengthy upto 450 rows i >deleted some rows. Hi shif, Thanks for posting the sp_lock output. Even though you deleted some rows, I think you've left in the ones that count. [quoted text, click to view] >if i have your mail id i can mail you the full text file. Normally, I prefer to keep newsgroup help in the groups and reserve my e-mail box for my loving mother, the usual load of spam and paying customers. But I understand you don't want to post the full output here, so go ahead - I'll allow it this time. My e-mail address is in the headers of all my messages - you'll have to remove "_NO_" and "_SPAM_" before using it (they are included to confuse the spam-bots). [quoted text, click to view] >here is the sp_who2 and sp_lock This output is even more clear that the previous output. Let's start with the sp_who2 output. This row: [quoted text, click to view] >SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID shows that connection with SPID 61 is attempting an insert, but has to>----- ------------------------------ ----- -------- ----- -------- ---------------- ------- ------ -------------- ------------------ ----- (snip) >61 sleeping sa . 56 sns_acc2 INSERT 7516 0 03/14 10:35:18 61 wait. It is apparently attempting to acquire a lock on a resource that is already locked by another application - one that currently holds SPID 56, as the entry "56" in the BlkBy columns proves. And this row: [quoted text, click to view] >SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID shows that the blocking connection is currently not doing anything.>----- ------------------------------ ----- -------- ----- -------- ---------------- ------- ------ -------------- ------------------ ----- (snip) >56 sleeping sa . . sns_acc2 AWAITING COMMAND 14906 25 03/14 10:28:29 56 Now, let's take at the entries in the sp_lock output that belong to SPID 61: [quoted text, click to view] >spid dbid ObjId IndId Type Resource Mode Status >------ ------ ----------- ------ ---- ---------------- -------- ------ (snip) >61 8 0 0 DB S GRANT >61 8 1410104064 0 TAB IX WAIT As you see, two locks only. One is granted, one is waiting. This one is the cause of the blocking. It's a table lock, and the mode is IX (intent-exclusive) - that means that the application needs to acquire an exclusive lock on either one page or one row that's part of a table. Since locks can always be upgraded to table locks, the right to do this has to be reserved to prevent deadlocking. To find out which table, use the values in dbid and ObjId. First, use dbid to identify the database name: SELECT db_name(8) Then switch to the database returned by the previous query, and find the name of the offending table: USE <db-name goes here> GO SELECT object_name(1410104064) My guess is that you'll find the database to be sns_acc2 and the table to be ap_spled. Finally, let's inspect the sp_lock output belonging to the blocking SPID 56. There's a whole lot of rows there. Most of them in dbid 2 (tempdb on most, maybe even all installations). Only one in dbid 8, but it has the same ObjId as in the previous row: [quoted text, click to view] >spid dbid ObjId IndId Type Resource Mode Status >------ ------ ----------- ------ ---- ---------------- -------- ------ (snip) >56 8 1410104064 0 TAB S GRANT And this one is the culprit. It's a shared table lock on the same table that the insert statement needs to get an intent-exclusive lock on. A shared lock is normally used for reading data - it allows others to read simultaneously, but it disallows exclusive locks (used for data modification), so that the data being read can't change. For obvious reasons, the shared lock on this table is not compatible with the requested intent-exclusive lock, so SPID 61 is put in a wait state until the lock is released. For some reason, his doesn't happen. Until you close the report generating application - then, either the application closes the connection (committing or rolling back any open transactions) or the connection gets dropped (incurring a forced rollback of the open transactions). All locks held by SPID 56 are then released, and the inserting applications finaly gets the lock it has been witing for :-) The next step should be to inspect the code of the application that generates the report. SQL Server's default behaviour is to release shared locks directly after the SELECT statement has finished executing. But that default behaviour can be changed. For a report generation that queries the same table several times, it might be necessary to prevent any modification between those queries, to prevent inconsistensies in the report (simple example: report debit first, then report credit - if any booking is inserted in between, the debit total won't match the credit total and your bookkeeper will have a fit). In such cases, the transaction isolation level is increased to either repeatable read or serializable (this forces SQL Server to hold on to shared locks until the transaction is finished). A transaction is started and all queries needed to generate the report are run. The first query will acquire the shared locks; they will be kept, so that no data can change until all queries are finished. When the report is complete, the transaction is committed, all locks are released and the report is presented to the user (or printed, or stored, or whatever). In your case, you should look for transactions that are started (with BEGIN TRANSACTION), but not ended (with either ROLLBACK TRANSACTION or COMMIT TRANSACTION). Note that some settings can cause an implicit BEGIN TRANSACTION. Also note that the process should not need any user input between the start and the end of the transaction, as users tend to go away from the computer and drink some coffee, unaware that their failure to click "OK" causes their collegues to be unable to work. I hope the above is helpful in determining and fixing your problem! Best, Hugo --
HI
Again back,actualy the problem was in the stored procedure i am using an update stmt,i checked by removing the update stmt and the hanginh prblm for 2 days not happend hope it's ok and thanks for your help.another thing if i want to include the update stsmt in the procedure like this What i have to do. update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT' OR #pd_temp.trans_type='PV') This stmt is creating prblm,can i give any commit here.Can you please help with a sample stmt. Thank you [quoted text, click to view] "Hugo Kornelis" wrote:
> On Sun, 13 Mar 2005 23:19:02 -0800, shif wrote: > > >Hi sir > >I forget the sp_lock i am adding here and it is lengthy upto 450 rows i > >deleted some rows. > > Hi shif, > > Thanks for posting the sp_lock output. Even though you deleted some > rows, I think you've left in the ones that count. > > >if i have your mail id i can mail you the full text file. > > Normally, I prefer to keep newsgroup help in the groups and reserve my > e-mail box for my loving mother, the usual load of spam and paying > customers. But I understand you don't want to post the full output here, > so go ahead - I'll allow it this time. My e-mail address is in the > headers of all my messages - you'll have to remove "_NO_" and "_SPAM_" > before using it (they are included to confuse the spam-bots). > > > >here is the sp_who2 and sp_lock > > This output is even more clear that the previous output. Let's start > with the sp_who2 output. This row: > > >SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID > >----- ------------------------------ ----- -------- ----- -------- ---------------- ------- ------ -------------- ------------------ ----- > (snip) > >61 sleeping sa . 56 sns_acc2 INSERT 7516 0 03/14 10:35:18 61 > shows that connection with SPID 61 is attempting an insert, but has to > wait. It is apparently attempting to acquire a lock on a resource that > is already locked by another application - one that currently holds SPID > 56, as the entry "56" in the BlkBy columns proves. > > And this row: > >SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID > >----- ------------------------------ ----- -------- ----- -------- ---------------- ------- ------ -------------- ------------------ ----- > (snip) > >56 sleeping sa . . sns_acc2 AWAITING COMMAND 14906 25 03/14 10:28:29 56 > shows that the blocking connection is currently not doing anything. > > Now, let's take at the entries in the sp_lock output that belong to SPID > 61: > > >spid dbid ObjId IndId Type Resource Mode Status > >------ ------ ----------- ------ ---- ---------------- -------- ------ > (snip) > >61 8 0 0 DB S GRANT > >61 8 1410104064 0 TAB IX WAIT > > As you see, two locks only. One is granted, one is waiting. This one is > the cause of the blocking. It's a table lock, and the mode is IX > (intent-exclusive) - that means that the application needs to acquire an > exclusive lock on either one page or one row that's part of a table. > Since locks can always be upgraded to table locks, the right to do this > has to be reserved to prevent deadlocking. > > To find out which table, use the values in dbid and ObjId. First, use > dbid to identify the database name: > SELECT db_name(8) > Then switch to the database returned by the previous query, and find the > name of the offending table: > USE <db-name goes here> > GO > SELECT object_name(1410104064) > > My guess is that you'll find the database to be sns_acc2 and the table > to be ap_spled. > > Finally, let's inspect the sp_lock output belonging to the blocking SPID > 56. There's a whole lot of rows there. Most of them in dbid 2 (tempdb on > most, maybe even all installations). Only one in dbid 8, but it has the > same ObjId as in the previous row: > > >spid dbid ObjId IndId Type Resource Mode Status > >------ ------ ----------- ------ ---- ---------------- -------- ------ > (snip) > >56 8 1410104064 0 TAB S GRANT > > And this one is the culprit. It's a shared table lock on the same table > that the insert statement needs to get an intent-exclusive lock on. A > shared lock is normally used for reading data - it allows others to read > simultaneously, but it disallows exclusive locks (used for data > modification), so that the data being read can't change. For obvious > reasons, the shared lock on this table is not compatible with the > requested intent-exclusive lock, so SPID 61 is put in a wait state until > the lock is released. For some reason, his doesn't happen. Until you > close the report generating application - then, either the application > closes the connection (committing or rolling back any open transactions) > or the connection gets dropped (incurring a forced rollback of the open > transactions). All locks held by SPID 56 are then released, and the > inserting applications finaly gets the lock it has been witing for :-) > > The next step should be to inspect the code of the application that > generates the report. SQL Server's default behaviour is to release > shared locks directly after the SELECT statement has finished executing. > But that default behaviour can be changed. For a report generation that > queries the same table several times, it might be necessary to prevent > any modification between those queries, to prevent inconsistensies in > the report (simple example: report debit first, then report credit - if > any booking is inserted in between, the debit total won't match the > credit total and your bookkeeper will have a fit). In such cases, the > transaction isolation level is increased to either repeatable read or > serializable (this forces SQL Server to hold on to shared locks until > the transaction is finished). A transaction is started and all queries > needed to generate the report are run. The first query will acquire the > shared locks; they will be kept, so that no data can change until all > queries are finished. When the report is complete, the transaction is > committed, all locks are released and the report is presented to the > user (or printed, or stored, or whatever). > > In your case, you should look for transactions that are started (with > BEGIN TRANSACTION), but not ended (with either ROLLBACK TRANSACTION or
[quoted text, click to view]
On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote: >HI >Again back,actualy the problem was in the stored procedure i am using an >update stmt,i checked by removing the update stmt >and the hanginh prblm for 2 days not happend hope it's ok and >thanks for your help.another thing if i want to include the update >stsmt in the procedure like this What i have to do. >update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no >from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT' >OR #pd_temp.trans_type='PV') >This stmt is creating prblm,can i give any commit here.Can you please help >with a sample stmt. >Thank you Hi shif, You have completely lost me now. I just re-read our complete exchange. First, you posted a stored procedure that creates a temp table #oi, inserts some data from ap_spled into it, then selects some data from a join of #oi and ap_spled. You also post an INSERT INTO ap_spled statement that hangs after this procedure has run. Now, you suddenly claim that you have removed an update statement that updates #pd_temp based on data in pay_hd. How is that possible? How can you remove a statement that never was there? Anyway - did you already check the db_name and object_name I suggested in my previous post? Did you inspect if the front-end code leaves any transactions open? I still believe that to be the cause of your problems. Fix your frontend to close the transaction after generating the report and your problems will probably go away. Best, Hugo --
Hi
I am sorry ,actualy i had copied a differend update syntax but both having same effect. thanks update #out_inv set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheq_no from pay_hd where #out_inv.tr_no = pay_hd.tr_no and (#out_inv.trans_type='PT' OR #out_inv.trans_type='PV') [quoted text, click to view] "Hugo Kornelis" wrote:
> On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote: > > >HI > >Again back,actualy the problem was in the stored procedure i am using an > >update stmt,i checked by removing the update stmt > >and the hanginh prblm for 2 days not happend hope it's ok and > >thanks for your help.another thing if i want to include the update > >stsmt in the procedure like this What i have to do. > >update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no > >from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT' > >OR #pd_temp.trans_type='PV') > >This stmt is creating prblm,can i give any commit here.Can you please help > >with a sample stmt. > >Thank you > > Hi shif, > > You have completely lost me now. I just re-read our complete exchange. > First, you posted a stored procedure that creates a temp table #oi, > inserts some data from ap_spled into it, then selects some data from a > join of #oi and ap_spled. You also post an INSERT INTO ap_spled > statement that hangs after this procedure has run. > > Now, you suddenly claim that you have removed an update statement that > updates #pd_temp based on data in pay_hd. How is that possible? How can > you remove a statement that never was there? > > Anyway - did you already check the db_name and object_name I suggested > in my previous post? Did you inspect if the front-end code leaves any > transactions open? I still believe that to be the cause of your > problems. Fix your frontend to close the transaction after generating > the report and your problems will probably go away. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
HI
Actualy i have posted is right only thing is that i have changed the table name instead of #oi i put some other name. [quoted text, click to view] "Hugo Kornelis" wrote:
> On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote: > > >HI > >Again back,actualy the problem was in the stored procedure i am using an > >update stmt,i checked by removing the update stmt > >and the hanginh prblm for 2 days not happend hope it's ok and > >thanks for your help.another thing if i want to include the update > >stsmt in the procedure like this What i have to do. > >update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no > >from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT' > >OR #pd_temp.trans_type='PV') > >This stmt is creating prblm,can i give any commit here.Can you please help > >with a sample stmt. > >Thank you > > Hi shif, > > You have completely lost me now. I just re-read our complete exchange. > First, you posted a stored procedure that creates a temp table #oi, > inserts some data from ap_spled into it, then selects some data from a > join of #oi and ap_spled. You also post an INSERT INTO ap_spled > statement that hangs after this procedure has run. > > Now, you suddenly claim that you have removed an update statement that > updates #pd_temp based on data in pay_hd. How is that possible? How can > you remove a statement that never was there? > > Anyway - did you already check the db_name and object_name I suggested > in my previous post? Did you inspect if the front-end code leaves any > transactions open? I still believe that to be the cause of your > problems. Fix your frontend to close the transaction after generating > the report and your problems will probably go away. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
[quoted text, click to view]
On Sun, 20 Mar 2005 07:19:01 -0800, shif wrote: >HI >Actualy i have posted is right only thing is that i have changed the table >name instead of #oi i put some other name. Hi shif, I'm sorry, but you've completely lost me by now. I suggest we go back to step 1. I can only try to understand what's happening if I can recreate your situation in my test database. For that, I need: * An SQL script to create the tables (i.e. CREATE TABLE statements). It is essential that you include all constraints and properties. If you have extra indexes, include the appropriate CREATE INDEX statements as well. (Check www.aspfaq.com/5006 as well). * An SQL script to fill the tables with some rows of sample data (i.e. INSERT statements). There's no need to post thousands of rows, just enough to show the structure of your data. You canb of course replace sensitive data with faked data, as long as the structure remains intact (e.g. replacing Bush with Name1 and Clinton with Name2 is okay; replacing both Bush and Cllinton with Name might not be). * The exact code of all triggers on your tables and of all stored procedures used in the operation. * The exact text of the SQL statements executed, and also indicate which statements are executed serially from one connection, and which are executed in parallel from another connection. If your SQL statements are generated by some middle tier, then you can use Profiler to catch the statements sent to the server. If you can post all of the above, I can try if I can find the cause of your problem. Oh ... another thing you might try is to run the report that causes the blocking from a Query Analyser window, then see if that causes blocking as well (keep the QA window woith the generated report open!). If it does, the reason is somewhere in the code that generates the report; if it doesn't introduce blocking, the problem is in the way your client software calls the procedure. Best, Hugo --
Hi Shif,
[quoted text, click to view] On Tue, 22 Mar 2005 21:41:34 +0100, Hugo Kornelis wrote: (snip) [quoted text, click to view] >I can only try to understand what's happening if I can recreate (snip)>your situation in my test database. For that, I need: Sorry for the long delay. I did get your e-mail, but I lacked the time to properly look into it. Some observations I made when reviewing your code: (1) Many of your stored procedures include a COMMIT instruction, but I could not find any BEGIN TRAN instruction. This can mean any of the following things: * The transaction is started in another procedure, that you did not include in the post, or * The transaction is started by a direct BEGIN TRAN call from the front-end, or * You are running with the IMPLICIT_TRANSACTIONS setting enabled. Based on your previous messages, I now think the last option is the most probable. But regardless, you should consider changing this. I believe that each stored procedure should end with the same amount of transactions open as when it's called, unless a critical error has occured (in which case all open transactions should be rolled back). And to make sure that this always happens, don't rely on implicit transactions - explicitly BEGIN TRANSACTION when you need it, and explicitly COMMIT (or ROLLBACK) TRANSACTION when you're done. That makes it easy to check if each procedure indeed commits all transactions it starts. (2) In the procedure pd_unpd (if I recall correctly, this is the proc that generates a report and starts the mysterious hanging applications, right?), you have two IF statements that execute an INSERT INTO, followed by a COMMIT TRAN - but the COMMIT TRAN is commented, so it won't be executed!! If my theory that you are running with SET IMPLICIT_TRANSACTIONS ON is correct, then the INSERT INTO will start a transaction, that will never be committed or rolled back. As long as the connection that start the report generation is not lost, the transaction will remain open. And all locks taken by the transaction will be held. Possible solutions (in random order): Make sure that all opened transactions are commited as soon as possible. Don't ever leave a transaction open while waiting for user input. Change your report generating code to use the transaction isolation level read commited. It currently is using either repeateble read or serializable. If that is necessary - all the more reason to commit the transaction as son as popssible. If that is not necessary, change it ASAP. (BTW, I could not find anything in the script you sent me to change the transaction isolation level, so I guess that this setting is controlled by the front-end application that makes the connection) I hope this helps! Best, Hugo --
Don't see what you're looking for? Try a search.
|
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |