Re: Concurrency Problem, How to skip the loked records from my query

gsrnadeem_at_hotmail.com (Gaser Nadeem) wrote in message news:<7a7dd679.0304111031.7e99e8a6_at_posting.google.com>...
> Hi All,
>
> I have a Problem in the database concerning the locking record.
> I have an SQL Statement which result in one record and locking it,
> unfortunately this Statement must be run more than once at the same
> time.
> so i need a solution the ignore the locked recored and go to the
> next one .
>
> Ex.
> select emp_name
> from employee
> where Age > 60
> for update of emp_status;
>
> the table content is:
> name age .....
> . . .....
> . . .....
> Jone 61 .....
> Mike 62 .....
> Lura 61 .....
> . . .....
> . . .....
>
> so if I run this SQL st. for more than a time, the first run will
> result to JONE, but the second and third and fourth run will wait
> until the first one commit its transaction.
> I need a solution so that the second SQL will skip JONE record
> since it is locked and result to MIKE ... and so on ......

Your query will return multiple rows, so it will lock multiple rows. In this case, the first query will lock the records for Jone, Mike, and Lura. In order for other sessions to be able to lock rows, they would have to issue statements which selected different rows.

Are you sure you really need to do this sort of pessimistic locking? Perhaps you can switch to an optimistic locking scheme.

Justin Cave Received on Fri Apr 11 2003 - 16:50:02 CDT