Re: [U2] SQL query locks
If this is UniVerse there is a uvconfig parameter MAXRLOCK that specifies how many record locks to take (on an INSERT, UPDATE or DELETE statement) before choosing to use a FILELOCK. Reducing this parameter will mean fewer record locks per query, at the cost of greater danger of another updating process being locked out of the table till the first process finishes its updates. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] SQL query locks
as far as I know, this issue was specific to UniData - as it was an SQL engine problem - not common client-side software. When you say 'it', to you mean that you are running a SQL update statetment with a WHERE clause and you see the records that were excluded from the query by the WHERE clause being locked? Or is it a less defined instance? Do you have an open support case on this, Charles? Regards. Wally Terhune SWG Client Support - Information Management Software U2 Support Architect b IBM U2 Client Support Team 4700 S. Syracuse St., Denver, CO 80237 Tel: (303) 773-7969 T/L 656-7969 Mobile: (303) 807-6222 [EMAIL PROTECTED] Register today for the premier U2 technical event! Stevenson, Charles Charles.Stevenso To [EMAIL PROTECTED] u2-users@listserver.u2ug.org Sent by: cc [EMAIL PROTECTED] stserver.u2ug.org Subject RE: [U2] SQL query locks 08/27/2007 07:10 PM Please respond to [EMAIL PROTECTED] er.u2ug.org Excellent. What about UniVerse? I've hit it...No: It's hit me there, too. cds -Original Message- From: Wally Terhune Sent: Monday, August 27, 2007 10:48 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] SQL query locks If this is UniData, we fixed a problem in this area at 6.1.7 (released 3/18/05): Issue 6917 - Problem Description UniData -- A lock release in SQL UPDATE failed, causing SQL to hold the locks for records that were excluded from the query with a WHERE clause. A fix to the lock checking utility corrected this issue. Hi, We have a Cold Fusion app that is using SQL queries. It's locking records, unfortunately as many as 100 at a time. Is there a way to do the query without locking records? We're not updating anything. Pam -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ [demime 1.01d removed an attachment of type image/jpeg which had a name of 2A005220.jpg] [demime 1.01d removed an attachment of type image/jpeg which had a name of 2A877667.jpg] [demime 1.01d removed an attachment of type image/gif which had a name of graycol.gif] [demime 1.01d removed an attachment of type image/gif which had a name of pic16518.gif] [demime 1.01d removed an attachment of type image/gif which had a name of ecblank.gif] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] SQL query locks
No, it UniVerse. Thanks, Pam Wally Terhune wrote: If this is UniData, we fixed a problem in this area at 6.1.7 (released 3/18/05): Issue 6917 - Problem Description UniData -- A lock release in SQL UPDATE failed, causing SQL to hold the locks for records that were excluded from the query with a WHERE clause. A fix to the lock checking utility corrected this issue. Wally Terhune SWG Client Support - Information Management Software U2 Support Architect b IBM U2 Client Support Team 4700 S. Syracuse St., Denver, CO 80237 Tel: (303) 773-7969 T/L 656-7969 Mobile: (303) 807-6222 [EMAIL PROTECTED] Register today for the premier U2 technical event! Pamela J Robbins [EMAIL PROTECTED] nn.eduTo Sent by: u2-users@listserver.u2ug.org, [EMAIL PROTECTED] Cattanea, Tom stserver.u2ug.org [EMAIL PROTECTED], Chris Dunbar [EMAIL PROTECTED] cc 08/27/2007 09:32 AMSubject [U2] SQL query locks Please respond to [EMAIL PROTECTED] er.u2ug.org Hi, We have a Cold Fusion app that is using SQL queries. It's locking records, unfortunately as many as 100 at a time. Is there a way to do the query without locking records? We're not updating anything. Pam -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ [demime 1.01d removed an attachment of type image/jpeg which had a name of 11186367.jpg] [demime 1.01d removed an attachment of type image/jpeg which had a name of 11145374.jpg] [demime 1.01d removed an attachment of type image/gif which had a name of graycol.gif] [demime 1.01d removed an attachment of type image/gif which had a name of pic29259.gif] [demime 1.01d removed an attachment of type image/gif which had a name of ecblank.gif] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] SQL query locks
This is from our Cold Fusion programmer: All our apps do are SQL SELCT statements (a few of which are complex with joins), but the only commands being sent are SELECT. I do not explicitly set a lock command or statement anywhere in the application. We are using Cold Fusion 7 connecting to UniVerse via IBMs JDBC driver. Pam Ray Wurlod wrote: If this is UniVerse there is a uvconfig parameter MAXRLOCK that specifies how many record locks to take (on an INSERT, UPDATE or DELETE statement) before choosing to use a FILELOCK. Reducing this parameter will mean fewer record locks per query, at the cost of greater danger of another updating process being locked out of the table till the first process finishes its updates. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[Fwd: RE: [Fwd: Re: [U2] SQL query locks]]
Can you update your post to ask specifically a)if someone has encountered locks while strictly doing SELECT statements? b)Does the with (nolock) work the same for UniVerse as it does for SQL Server? c)Do transaction isolation levels apply to users or transaction types, and how can they be adjusted... Thanks Pam. I think we're getting somewhere here. Chris -Original Message- From: Pamela J Robbins [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 28, 2007 10:27 AM To: Chris Dunbar Subject: [Fwd: Re: [U2] SQL query locks] Original Message Subject:Re: [U2] SQL query locks Date: Tue, 28 Aug 2007 16:58:04 +1000 From: Ray Wurlod [EMAIL PROTECTED] Reply-To: u2-users@listserver.u2ug.org To: u2-users@listserver.u2ug.org If this is UniVerse there is a uvconfig parameter MAXRLOCK that specifies how many record locks to take (on an INSERT, UPDATE or DELETE statement) before choosing to use a FILELOCK. Reducing this parameter will mean fewer record locks per query, at the cost of greater danger of another updating process being locked out of the table till the first process finishes its updates. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] SQL query locks
When a UniVerse/SQL SELECT is in progress, each group (one at a time) is latched to prevent the current group from being modified. However, these group latches are released immediately the SELECT has finished processing that group, so that ought not to be an issue apart from the minor possibility of contention on those groups. Certainly lots of locks ought not to be obtained by a SELECT. (If you set transaction isolation level high (say 3 or 4), then you may start seeing FS or even IX file locks taken, but not many record-level locks.) Might be worth bouncing this one off support. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] SQL query locks
Take a look at the nolock query hint. On 8/27/07, Pamela J Robbins [EMAIL PROTECTED] wrote: Hi, We have a Cold Fusion app that is using SQL queries. It's locking records, unfortunately as many as 100 at a time. Is there a way to do the query without locking records? We're not updating anything. Pam -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- Don Kibbey --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] SQL query locks
Just a guess, but this migh have to do with your transaction isolation level. Which DB are you using? UniVerse or UniData? I assume that the ColdFusion app is using ODBC to connect to the DB? Pamela J Robbins wrote: Hi, We have a Cold Fusion app that is using SQL queries. It's locking records, unfortunately as many as 100 at a time. Is there a way to do the query without locking records? We're not updating anything. Pam -- Geoffrey Mitchell Programmer/Analyst Home Decorator's Collection 314-684-1062 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] SQL query locks
If this is UniData, we fixed a problem in this area at 6.1.7 (released 3/18/05): Issue 6917 - Problem Description UniData -- A lock release in SQL UPDATE failed, causing SQL to hold the locks for records that were excluded from the query with a WHERE clause. A fix to the lock checking utility corrected this issue. Wally Terhune SWG Client Support - Information Management Software U2 Support Architect b IBM U2 Client Support Team 4700 S. Syracuse St., Denver, CO 80237 Tel: (303) 773-7969 T/L 656-7969 Mobile: (303) 807-6222 [EMAIL PROTECTED] Register today for the premier U2 technical event! Pamela J Robbins [EMAIL PROTECTED] nn.eduTo Sent by: u2-users@listserver.u2ug.org, [EMAIL PROTECTED] Cattanea, Tom stserver.u2ug.org [EMAIL PROTECTED], Chris Dunbar [EMAIL PROTECTED] cc 08/27/2007 09:32 AMSubject [U2] SQL query locks Please respond to [EMAIL PROTECTED] er.u2ug.org Hi, We have a Cold Fusion app that is using SQL queries. It's locking records, unfortunately as many as 100 at a time. Is there a way to do the query without locking records? We're not updating anything. Pam -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ [demime 1.01d removed an attachment of type image/jpeg which had a name of 11186367.jpg] [demime 1.01d removed an attachment of type image/jpeg which had a name of 11145374.jpg] [demime 1.01d removed an attachment of type image/gif which had a name of graycol.gif] [demime 1.01d removed an attachment of type image/gif which had a name of pic29259.gif] [demime 1.01d removed an attachment of type image/gif which had a name of ecblank.gif] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] SQL query locks
Excellent. What about UniVerse? I've hit it...No: It's hit me there, too. cds -Original Message- From: Wally Terhune Sent: Monday, August 27, 2007 10:48 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] SQL query locks If this is UniData, we fixed a problem in this area at 6.1.7 (released 3/18/05): Issue 6917 - Problem Description UniData -- A lock release in SQL UPDATE failed, causing SQL to hold the locks for records that were excluded from the query with a WHERE clause. A fix to the lock checking utility corrected this issue. Hi, We have a Cold Fusion app that is using SQL queries. It's locking records, unfortunately as many as 100 at a time. Is there a way to do the query without locking records? We're not updating anything. Pam -- Pamela J Robbins New Bolton Center Senior Programmer AnalystSchool of Veterinary Medicine [EMAIL PROTECTED] University of Pennsylvania 610-925-6438 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
