Solved

SQL Lock Row until process done

Posted on 2003-11-17
26
489 Views
Last Modified: 2007-12-19
Hi,
I have compont which will get information from the sql after process the inofmration will update the new information to the sql database.
Here is my problem, the component will be call by more than two applications at same time and it is not a multithread component.
I want the first one call the sql lock the row and second one need to wait for first one finish.
What should I do?
Thanks!
jpc
0
Comment
Question by:jpchen
  • 10
  • 9
  • 4
  • +1
26 Comments
 
LVL 7

Expert Comment

by:jj819430
Comment Utility
Well one option would be to put in a "Readable" Column.
You just do something like call the column "Readable"
Set it = 0 if it is locked
set it = 1 if it is unlocked

When the one application goes to grad its row, set the "Readable" = 0;
Then when the application is done, set the Readable = 1;

then the other application can just check to see if it is readable

This is just an idea I have. There may be a better way out there.
0
 
LVL 1

Expert Comment

by:darkxenn
Comment Utility
you dont say how you access the database... if you want to keep the database, and particularly the table you are accessing locked while you do something, keep the datareader that you are using open

OleDbConnection myconnection;
myconnection = new OleDbConnection();
OleDbCommand mycommand;
mycommand = new OleDbCommand();
OleDbDataReader myReader;

// set your connection strings and command strings, etc...

myconnection.Open();
myReader = mycommand.ExecuteReader();

while ( myReader.Read() )
{
//access your data how you want to
}

// put your code to do whatever you want to your information...

mycommand.CommandText = "UPDATE [MyTable] HOWEVER YOU WANT TO"
myReader.Close();
mycommand.ExecuteNonQuery();
myconnection.close();


is that about what you are looking for?


if you are still looking for an even more locked down thing... keep a "locked" field in another table, that you check before doing any SQL... this can be a simple "1" or "0"

so your structure will look like this

-- pseudocode --

if ([locked_table] is not locked)
{
       [locked table].value = 1
       //do whatever you need to do
       [locked table].value = 0
}


this idea is just basically taking simple ideas of concurrency, and applying a simple lock system to the resources...
hope this helps...
-Xen
   
0
 
LVL 1

Expert Comment

by:darkxenn
Comment Utility
sorry, didnt mean the extraneous post... was writing mine while the other was posted

best,
Xen
0
 
LVL 7

Expert Comment

by:jj819430
Comment Utility
Xen,
If you have two completely seperate applications trying to access a SQL DB, will keeping a reader open actually block the other application?
I would think not. But I have never tried that, and it may become an issue with a project I am working on.
0
 

Author Comment

by:jpchen
Comment Utility
Hi jj819430,
If I add a "Readable" Column, can you let me know how can I tell another process to wait?
Thanks!
jpc
0
 

Author Comment

by:jpchen
Comment Utility
I am using SQLClient.
I think add a "Readable" Column is a good idea, but I just afraid if two aplications are calling SQL at same time, what could be happend?
0
 
LVL 7

Expert Comment

by:jj819430
Comment Utility
sure,
But thinking of it, I would do it Xens way with another table.
I will do the code for a readable column real quick for you, (may have syntax errors);

System.Object ReadableCheck = new object;
string SQLString = // create your select from table where field = whatever row you are looking for and pull the "readable"
SqlCommand RCheck = new SqlCommand(SQLString,myConn);
ReadableCheck = SqlCommand.ExecuteScalar;
Convert.ToInt64(ReadableCheck);

ReadableCheck is now an int you can compare against.
0
 
LVL 7

Expert Comment

by:jj819430
Comment Utility
if (ReadableCheck == 1)
{
//wait a bit
}

if (ReadableCheck == 0)
{
good to go
}
0
 

Author Comment

by:jpchen
Comment Utility
I don't know how long need to wait for the proces has been done, what is the best way for the wait loop?
0
 
LVL 7

Expert Comment

by:jj819430
Comment Utility
How much does efficiency matter to you?
If you want just stick it in a while loop.


while (ReadableCheck == 1)
{
ReadableCheck = DoAReadableCheck();
}
0
 

Author Comment

by:jpchen
Comment Utility
Your solution is ok only one exection, if both application doing the same time, what should be happend?
If I lock the row, is that second process make a sql connection will wait for the row until unlock?
0
 
LVL 7

Expert Comment

by:jj819430
Comment Utility
ok, here is what I see happening.

there are 3 possibilities.
1. Second application opens it before the 1st application
     The second application would be able to read it because it would still be unlocked.
2. Second application opens it after the 1st application
     the second application has to wait because it is locked.
3. Second and 1st do it at the same time.
     Not really possible. Will fall under option 1 or option 2.

if you NEED to make sure the first one has gone through the table rows before the second one can read any of them, then what you can do is set it so it starts with all of the readable values at 1.
as the first one goes through it changes it to a 0
0
 

Author Comment

by:jpchen
Comment Utility
Thanks, I am still having question about this.
I don't know which aplication will be run first, when, and how long.
So if I use you sloution, the option 3 will be ok, or it is still possible?
And, what is the best loop to way, I don't think I should keep query db all the time.
Thanks again
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:jj819430
Comment Utility
just for clarity. You want to have it make sure that the update application has gone through first?

and for the loop you are going to have to continually check in order to be efficient. You can put in a pause of some kind. But it really won't make a difference overall.
0
 

Author Comment

by:jpchen
Comment Utility
Yes, I wan to make sure the update application has gone through first.
Thanks!
0
 
LVL 7

Expert Comment

by:jj819430
Comment Utility
Ok... so Build your Database with the extra column.
Have it start automatically at 1; (1 = false for application 1 having gone through);
then when your application has gone through set it to 0;

Have the second application see if the row has a 0 or 1.
if it has a 1 ignore it, or loop, or if it is with the user state that the data is not updated yet.

if I were doing the loop solution, I would do a constant check with the data. So long as you know that the data will be updated. (The real risk here is that if you have a Large database. It may take a few minutes for your first application to run through, and thus your second would be stuck.)

So I would do the check. Because I doubt that the DB is going to get that large. If you are just querying one row atleast.
If you are selecting many rows or all to be displayed, then I would use the Readable check just to see if that one gets displayed or not. Or if you want it displayed you could do it so that that one can not be edited.

(Am I on the train of thought you are on?)
JJ
0
 

Author Comment

by:jpchen
Comment Utility
Thanks for your solution, I have mention I will have more than two application using my component which I am not sure how many.
I am still looking for the best solution.
Thanks!
jpc
0
 
LVL 7

Assisted Solution

by:jj819430
jj819430 earned 100 total points
Comment Utility
OK, If I can think of anything better I will post it. (when is the latest you can have a solution for this?)
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
Just reading all the posts ...
Your problem seems to be a traditional recordlocking problem ...

Did you already thought using a Transaction for your problem? When starting a Transaction (BeginTransaction) you can specify the Isolationlevel. So, here you set who can view/change recordfields of a LOCKED record ...

The time-out etc. is all done by the Transaction itselves ... If you handle it all yourselves (with an additional field?), when a records stays locked (who knows why ...) your program will hang ... The Transaction will throw an exception, that you can handle ...

Using Transactions is very simple, ... transactions offer the developer the ability to enforce data integrity by making sure multiple operations can be treated by the engine as an "all or nothing" proposition, thereby never allowing the database to end up in an inconsistent state.
0
 

Author Comment

by:jpchen
Comment Utility
Need this solution ASAP.
Thanks!
jpc
0
 

Author Comment

by:jpchen
Comment Utility
Hi wsteegmans,
Can you show a simple how to use it?
Thanks!
jpc
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
I think you have here a nice story:
http://www.c-sharpcorner.com/asp/Code/TransactionsInASPNETDPL.asp

Look also in the MSDN ...

I really should learn this stuff ... because transactions are very important in creating robust DB-Programs!
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
or this one: http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=451

Maybe you can poste some code where you build in your extra field thing ...
I'll try to integrate the Transaction-clauses ...
0
 
LVL 1

Assisted Solution

by:darkxenn
darkxenn earned 50 total points
Comment Utility
the reader will most definately lock the database... that causes problems sometimes

-Xen
0
 

Author Comment

by:jpchen
Comment Utility
Hi,
If I use the transaction with 'XLOCK', when other application calling this function. what is best way for doing the wait? such as wait for 12 seconds.
Thanks!
jpc

            private void updateRecord(int i)
            {      
                  SqlConnection cnn = null;
                  SqlCommand cmd = null;
                  SqlTransaction myTrans;
                  SqlDataReader recordSet;
                  
                  string queryString = "SELECT * FROM "+DBTableName+" with(XLOCK) where FileLocation='"+sFileLocation+"'";
                  try
                  {
                        cnn = new SqlConnection();
                        cnn.ConnectionString = "Data Source=(local);"+"Initial Catalog="+DBName+";Integrated Security=SSPI;Enlist=false;Connect Timeout=30";

                        cmd=cnn.CreateCommand();
                        cmd.CommandType=CommandType.Text;
                        cmd.CommandText = queryString;
                        cnn.Open();
                        myTrans = cnn.BeginTransaction();
                        cmd.Transaction = myTrans;
                        recordSet = cmd.ExecuteReader(CommandBehavior.SingleResult);
                        if (recordSet.Read())
                        {
//doing other process here
                        }
                        myTrans.Commit();
                        cnn.Close();
                  }
                  catch (Exception ex) // Catch other exception message
                  {
                  }
                  finally //Close DB objects
                  {
                        if(cnn != null) cnn.Close();
                  }
            }
0
 
LVL 7

Accepted Solution

by:
wsteegmans earned 100 total points
Comment Utility
Now, you're combining a real SQL Lock (with XLOCK) and a lock by the Transaction-functinality ... Never tried that before ...

But to answer your question, because you use a SqlCommand object to execute your request, you can set the CommandTimeOut Property of the Command Object. This timeout indicates how long to wait while executing a command before terminating the attempt and generating an error. Try it to set two 12 seconds ...

More about the CommandTimeOut Property:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdapro02_5.asp
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now