Another SQL Query Question

[renzlo]

Hi, All,

Sample Table
Job_ID     Job_Name     Status
1          TEST00001    FOR KE
2          TEST00002    FOR KE
3          TEST00003    FOR KE
4          TEST00004    FOR KE
5          TEST00005    FOR KE

I have sql query that will get a jobname where status is equal to 'FOR KE' and I have multiple users that will query that.

Now, what if two queries was executed at the same time? Will the two users get the same jobname? How can I avoid that?

Thanks in advance.

Sincerely,

renzlo

[wen_cai]

If it is just to view the data there is not much problem. However, if multiple user is trying to modify the data then we can use transaction which will lock the data. Do becareful when using transaction as it can affect the performance.

[renzlo]

sorry for not being clear, it will be a big deal for me because I have Data Entry program that will auto-get a jobname using that query, I just want to avoid two or more same jobnames being processed by multiple users.

[adam_k]

You need to share more details. What happens after you get the job name?
If you store it in a table then you can join to that table and exclude all records in that table. By doing this you can use a transaction and eliminate the possibility of 2 users getting the same job.

If you change the status from FOR KE to something else, then you can also use a transaction and lock the record to a specific user.

If you just read the record and don't write/update/delete anything in the database then you can't use a transaction.

Is this a heavy load table?

If you want to avoid the transaction - as wen cai said it can damage performance - and you are updating the status then you can update the table and use output to get the job name.

[Reverend Jim]

A query to retrieve a job name where status = "FOR KE" will return (using your data) five records. In your example, "get" is ambiguous. Do you mean "get a record from the database" or do you mean "generate the next available job name"?

Let's assume you mean "generate". In that case, the query that will create the new record with the new job name should be done using a lock type of LockPessimistic. This will prevent two concurrent insertions.

[renzlo]

thanks for the reply guys, and again sorry for not being so clear...here's the more detailed scenario:

I have created two programs:

Sample_tbl
Job_ID  J_USER     Job_Name     Status
1       (null)     TEST00001    FOR KE
2       (null)     TEST00002    FOR KE
3       (null)     TEST00003    FOR KE
4       (null)     TEST00004    FOR KE
5       (null)     TEST00005    FOR KE

1. Data Entry - this is the application being used by my staffs to load a workload. It has a get jobname button so that the user can get a workload for the day. The jobnames are in the database (see sample above), that button has this query "SELECT JOB_NAME FROM SAMPLE_TBL WHERE JOB_ID > 0 AND STATUS='FOR KE'" and once the user get the jobname the program will update the jobname in database by changing the status to "ONGOING" and changing the null value of J_USER to the current user (for eg. AX00) and once they're done in the jobname, the program will set the status to "FINISHED".

2. Jobname Controller - this shows the whole table in the datagrid, this just monitors the user if their doing their workload.

Now, my problem is that since multiple users are sending queries to the database and sometimes at the same time, some users get the same jobname, I want to avoid that, is there a query that will prevent this?

[Reverend Jim]

As I said, as long as you use a lock (I suggest LockPessimistic) there should be no problem. When you use a lock, the recommendation is to have the lock for only as long as is absolutely necessary.

[renzlo]

can you give me a sample query or code?

[wen_cai]

The code below is using the transaction funtion. Commit is to save your changed data into the table. When you start a transaction, table that is being used will be lock and will be release once you have commited it back to the table or you have cancel the changes made. This will still allow you to read from the table but does not allow you to write to the table while the transaction is in progress.

BEGIN TRANSACTION
SQL Statement
Commit Transaction (OR use "rollback" statement to cancel the change)

I think you can get away with using triggers. I used triggers when there is a few number of users updating the tables and will check if the changes has been made to the row. Take a look at triggers and consider which you prefer to use.

[renzlo]

thank you wen_cai, i'll look at the triggers and will make a choice, would you mind giving sample of that?

[wen_cai]

I am assuming your J_USER will be assign if they are on the job. If that is the case, I will use a before trigger to check if J_USER is null before inserting/updating the data.

CREATE trigger TRIGERNAME 
  BEFORE update on SAMPLETABLE
  for each row
BEGIN

  IF (SAMPLETABLE.J_USER ==  null)
    BEGIN
        YOUR INSERT / UPDATE SQL HERE
    END
  ELSE
    BEGIN
        set @err_message = 'Job has been assign.'
        RAISERROR(@err_message,11,1)
    END

END TRIGERNAME;

[renzlo]

thanks wen_cai, I'm sorry for being too slow, I'm kinda new in SQL where will I put this piece of code?

[wen_cai]

These are SQL "codes" so you declare them in your database. Triggers works when a certain command is used like update or delete or insert. If you have a before trigger declare and is tied to your update command, it will fire before you update your row/table when you try to update it.

As for the transaction, it is using locks. Once the transaction began, it will lock the table to only allow other users to view it's data but will not allow other users to modify them until the lock has been release. The lock on the table will be release when you have completed the transaction. The code for the transaction is for the table but you can always use a lock on just a row or column or the whole database. That depend on your need and for you to choose which is better for your cause.

[renzlo]

Thank you, wen_cai, It seems transaction fits my needs. Can you give me some sample query for transaction just what you've done with trigger?

[M.Waqas Aslam]

well , there is an idea to deal with this situation , is that , you can just add a column to your db , name isViewing datatype bit , now if user one get records it will not only get records to you but also update isViewing true , and you can just add simple line to your query where isViewing = false ,
this is just an idea , please no hard feelings if you think i am wrong , :)

Best Regards

[renzlo]

Thanks for the idea waqasaslammmeo, currently I am using transactions.