SQL Lock Row until process done

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
jpchenAsked:
Who is Participating?
 
wsteegmansConnect With a Mentor Commented:
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
 
jj819430Commented:
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
 
darkxennCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
darkxennCommented:
sorry, didnt mean the extraneous post... was writing mine while the other was posted

best,
Xen
0
 
jj819430Commented:
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
 
jpchenAuthor Commented:
Hi jj819430,
If I add a "Readable" Column, can you let me know how can I tell another process to wait?
Thanks!
jpc
0
 
jpchenAuthor Commented:
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
 
jj819430Commented:
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
 
jj819430Commented:
if (ReadableCheck == 1)
{
//wait a bit
}

if (ReadableCheck == 0)
{
good to go
}
0
 
jpchenAuthor Commented:
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
 
jj819430Commented:
How much does efficiency matter to you?
If you want just stick it in a while loop.


while (ReadableCheck == 1)
{
ReadableCheck = DoAReadableCheck();
}
0
 
jpchenAuthor Commented:
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
 
jj819430Commented:
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
 
jpchenAuthor Commented:
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
 
jj819430Commented:
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
 
jpchenAuthor Commented:
Yes, I wan to make sure the update application has gone through first.
Thanks!
0
 
jj819430Commented:
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
 
jpchenAuthor Commented:
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
 
jj819430Connect With a Mentor Commented:
OK, If I can think of anything better I will post it. (when is the latest you can have a solution for this?)
0
 
wsteegmansCommented:
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
 
jpchenAuthor Commented:
Need this solution ASAP.
Thanks!
jpc
0
 
jpchenAuthor Commented:
Hi wsteegmans,
Can you show a simple how to use it?
Thanks!
jpc
0
 
wsteegmansCommented:
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
 
wsteegmansCommented:
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
 
darkxennConnect With a Mentor Commented:
the reader will most definately lock the database... that causes problems sometimes

-Xen
0
 
jpchenAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.