OLE dotNET: LOCK MODE NO WAIT/SELECT...FOR UPDATE

Posted in 2009

Topics: SQL Development & Query Writing, Server Administration, Triggers, Constraints & Referential Integrity

JONATHAN — Wed, 14 Jan 2009 19:01:55 — source: IIUG Forums & Mailing Lists

(Delphi 2006 for .NET 1.1, WinForms, IBM.Data.Informix.dll v2.90.6.0) I am attempting to write a desktop alert application to test for locks on a database. I am using transaction processing with "SET LOCK MODE TO NO WAIT" and "SELECT...FOR UPDATE". When I run a test using Informix SQL Editor, I get the behavior I expect: Row is locked by uncommitted INSERT in one session, second session attempting SET LOCK MODE TO NO WAIT/SELECT...FOR UPDATE immediately returns an error (without waiting) indicating the record is locked. When I attempt this via my .NET application, however, my SELECT statement (via .ExecuteScalar) does NOT trigger an exception when the row/table is locked; thus, I have no way of knowing whether or not a lock situation exists. If I set LOCK MODE TO WAIT, the .ExecuteScalar hangs until the lock is indeedreleased. I would expect an exception to be triggered with LOCK MODE NO WAIT if a lock exists. Anybody have any idea what I am missing? Any suggestions, information, or direction would be greatly appreciated. Following is part of my code if it will be of any assistance: // Default Result to FALSE Result := false; // Begin Transaction dbtxTransaction := pv_dbConnect.BeginTransaction(IsolationLevel.ReadCommitted); try // using try/finally to ensure TRANSACTION ROLLBACK // Create SQL Command Object, setting Database Connection and Transaction dbcCommand := OleDbCommand.Create('', pv_dbConnect, dbtxTransaction); // - Set TIMEOUT to 5 seconds dbcCommand.CommandTimeout := 5; // Set LOCK MODE to NO WAIT dbcCommand.CommandText := 'SET LOCK MODE TO NO WAIT'; dbcCommand.ExecuteNonQuery(); // Construct SELECT...FOR UPDATE sSQL := 'SELECT ' + 'table_id ' + 'FROM table ' + 'WHERE table_id = (SELECT MIN(table_id) FROM table) ' + 'FOR UPDATE'; try // using try/finally expecting EXCEPTION on LOCK // Execute SELECT...FOR UPDATE (to TEST for LOCK, will automatically be rolledback) dbcCommand.CommandText := sSQL; dbcCommand.ExecuteReader(); // Query SUCCESS (NO LOCK), set Result to TRUE Result := true; // NOTE: SELECT...FOR UPDATE automatically ROLLEDBACK below in FINALLY except on e: exception do // .ExecuteScalar Exception po_sErrorMsg := 'EXCEPTION: OleDbCommand.ExecuteScalar (' + e.ToString.Trim() + ')' + g_sLF + ' SQL: ' + dbcCommand.CommandText; end; finally // ROLLBACK TRANSACTION (on EITHER Success OR Failure) dbtxTransaction.Rollback; end;

↪ replying to JONATHAN

dharmendra — Thu, 15 Jan 2009 04:25:05 — source: IIUG Forums & Mailing Lists

Hi Jonathan, I am not much familiar with .NET code and therefore can't say for sure why it's not throwing an exception. You may try following: create a Stored Procedure, move your SET LOCK...and SELECT statement in Stored PRocedure. Check the status of SQL statement in Stored PRocedure and return the status code back (successful or unsuccessful). In .NET code, replace your SELECT query with EXECUTE procedure statement and check the return value to determine whether record was locked or not.. Dharmendra > To: ids@iiug.org> From: jweekes@jacksongov.org> Subject: OLE dotNET: LOCK MODE NO WAIT/SELECT...FOR UPDATE [14537]> Date: Wed, 14 Jan 2009 14:01:55 -0500> > (Delphi 2006 for .NET 1.1, WinForms, IBM.Data.Informix.dll v2.90.6.0) > > I am attempting to write a desktop alert application to test for locks on a > database. I am using transaction processing with "SET LOCK MODE TO NO WAIT" > and "SELECT...FOR UPDATE". > > When I run a test using Informix SQL Editor, I get the behavior I expect: Row > is locked by uncommitted INSERT in one session, second session attempting SET > LOCK MODE TO NO WAIT/SELECT...FOR UPDATE immediately returns an error (without > waiting) indicating the record is locked. > > When I attempt this via my .NET application, however, my SELECT statement (via > ..ExecuteScalar) does NOT trigger an exception when the row/table is locked; > thus, I have no way of knowing whether or not a lock situation exists. If I > set LOCK MODE TO WAIT, the .ExecuteScalar hangs until the lock is indeed > released. I would expect an exception to be triggered with LOCK MODE NO WAIT > if a lock exists. > > Anybody have any idea what I am missing? Any suggestions, information, or > direction would be greatly appreciated. Following is part of my code if it > will be of any assistance: > > // Default Result to FALSE > Result := false; > > // Begin Transaction > dbtxTransaction := > pv_dbConnect.BeginTransaction(IsolationLevel.ReadCommitted); > > try // using try/finally to ensure TRANSACTION ROLLBACK > > // Create SQL Command Object, setting Database Connection and Transaction > > dbcCommand := OleDbCommand.Create('', pv_dbConnect, dbtxTransaction); > > // - Set TIMEOUT to 5 seconds > > dbcCommand.CommandTimeout := 5; > > // Set LOCK MODE to NO WAIT > > dbcCommand.CommandText := 'SET LOCK MODE TO NO WAIT'; > > dbcCommand.ExecuteNonQuery(); > > // Construct SELECT...FOR UPDATE > > sSQL > > := 'SELECT ' > > + 'table_id ' > > + 'FROM table ' > > + 'WHERE table_id = (SELECT MIN(table_id) FROM table) ' > > + 'FOR UPDATE'; > > try // using try/finally expecting EXCEPTION on LOCK > > // Execute SELECT...FOR UPDATE (to TEST for LOCK, will automatically be > rolledback) > > dbcCommand.CommandText := sSQL; > > dbcCommand.ExecuteReader(); > > // Query SUCCESS (NO LOCK), set Result to TRUE > > Result := true; > > // NOTE: SELECT...FOR UPDATE automatically ROLLEDBACK below in FINALLY > > except on e: exception do > > // .ExecuteScalar Exception > > po_sErrorMsg := 'EXCEPTION: OleDbCommand.ExecuteScalar (' + e.ToString.Trim() > + ')' > > + g_sLF + ' SQL: ' + dbcCommand.CommandText; > > end; > finally > > // ROLLBACK TRANSACTION (on EITHER Success OR Failure) > > dbtxTransaction.Rollback; > end; > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live: Keep your life in sync. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_012009

↪ replying to JONATHAN

Everett — Thu, 15 Jan 2009 15:42:42 — source: IIUG Forums & Mailing Lists

Jonathan- See below > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > JONATHAN WEEKES > Sent: Wednesday, January 14, 2009 1:02 PM > To: ids@iiug.org > Subject: OLE dotNET: LOCK MODE NO WAIT/SELECT...FOR UPDATE [14537] > > (Delphi 2006 for .NET 1.1, WinForms, IBM.Data.Informix.dll v2.90.6.0) > > I am attempting to write a desktop alert application to test for locks on > a > database. I am using transaction processing with "SET LOCK MODE TO NO > WAIT" > and "SELECT...FOR UPDATE". > > When I run a test using Informix SQL Editor, I get the behavior I expect: > Row > is locked by uncommitted INSERT in one session, second session attempting > SET > LOCK MODE TO NO WAIT/SELECT...FOR UPDATE immediately returns an error > (without > waiting) indicating the record is locked. > > When I attempt this via my .NET application, however, my SELECT statement > (via > ..ExecuteScalar) does NOT trigger an exception when the row/table is > locked; > thus, I have no way of knowing whether or not a lock situation exists. If > I > set LOCK MODE TO WAIT, the .ExecuteScalar hangs until the lock isindeed > released. I would expect an exception to be triggered with LOCK MODE NO > WAIT > if a lock exists. > > Anybody have any idea what I am missing? Any suggestions, information, or > direction would be greatly appreciated. Following is part of my code if it > will be of any assistance: > > // Default Result to FALSE > Result := false; > > // Begin Transaction > dbtxTransaction := > pv_dbConnect.BeginTransaction(IsolationLevel.ReadCommitted); > > try // using try/finally to ensure TRANSACTION ROLLBACK > > // Create SQL Command Object, setting Database Connection and Transaction > > dbcCommand := OleDbCommand.Create('', pv_dbConnect, dbtxTransaction); > > // - Set TIMEOUT to 5 seconds > > dbcCommand.CommandTimeout := 5; > > // Set LOCK MODE to NO WAIT > > dbcCommand.CommandText := 'SET LOCK MODE TO NO WAIT'; I think your problem is that the lock mode command is executed singly and doesn't persist to the next statement. Have you tried stringing your lock mode command together with your select: dbcCommand.CommandText := 'SET LOCK MODE TO NOT WAIT; ' + 'SELECT ' + 'table_id ' + 'FROM table ' + 'WHERE table_id = (SELECT MIN(table_id) FROM table) ' + 'FOR UPDATE'; > > dbcCommand.ExecuteNonQuery(); > > // Construct SELECT...FOR UPDATE > > sSQL > > := 'SELECT ' > > + 'table_id ' > > + 'FROM table ' > > + 'WHERE table_id = (SELECT MIN(table_id) FROM table) ' > > + 'FOR UPDATE'; > > try // using try/finally expecting EXCEPTION on LOCK > > // Execute SELECT...FOR UPDATE (to TEST for LOCK, will automatically be > rolledback) > > dbcCommand.CommandText := sSQL; > > dbcCommand.ExecuteReader(); > > // Query SUCCESS (NO LOCK), set Result to TRUE > > Result := true; > > // NOTE: SELECT...FOR UPDATE automatically ROLLEDBACK below in FINALLY > > except on e: exception do > > // .ExecuteScalar Exception > > po_sErrorMsg := 'EXCEPTION: OleDbCommand.ExecuteScalar (' + > e.ToString.Trim() > + ')' > > + g_sLF + ' SQL: ' + dbcCommand.CommandText; > > end; > finally > > // ROLLBACK TRANSACTION (on EITHER Success OR Failure) > > dbtxTransaction.Rollback; > end; > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to dharmendra

JONATHAN — Thu, 15 Jan 2009 15:58:18 — source: IIUG Forums & Mailing Lists

Thanks, Dharmendra; this is a great idea--way to "think outside of the box." :) I had modified my program to "SET WAIT MODE TO WAIT 20", save before and after time, and if wait time (after time - start time) >= 20 seconds, assume a lock exists. This seems to work, but I think your stored procedure idea would be much more efficient. Thanks!!

↪ replying to Everett

JONATHAN — Thu, 15 Jan 2009 16:24:39 — source: IIUG Forums & Mailing Lists

RE: > I think your problem is that the lock mode command is executed singly and > doesn't persist to the next statement. Have you tried stringing your lock > mode command together with your select: > > dbcCommand.CommandText := 'SET LOCK MODE TO NOT WAIT; ' > + 'SELECT ' > + 'table_id ' > + 'FROM table ' > + 'WHERE table_id = (SELECT MIN(table_id) FROM table) ' > + 'FOR UPDATE'; Thanks for the reply, Everett. Unfortunately, stringing the commands together is what I tried initially, but I received the following error: EIX000: (-555) Cannot use a select or any of the database statements in a multi-query prepare. The statement text that is presented with this PREPARE statement has multiple statements divided by semicolons, and one is a SELECT, DATABASE, CREATE DATABASE, or CLOSE DATABASE statement. These statements must always be prepared as one-statement texts. Check the statement text string, and make sure that you intended multiple statements. If you did, revise the program to execute these four statement types alone. Since I am using transaction processing (begin/rollback work), sending SET LOCK MODE as a separate command does persist to the next command--I can change from NOT WAIT to WAIT and get different results, though none of them desirable since no exception is triggered.