| Dirty read issues [message #637366] |
Fri, 15 May 2015 10:41  |
 |
raj_te
Messages: 46
Registered: August 2013
Location: INDIA
|
Member |
|
|
|
I have a table and two different jobs are populating that table at different times. Sometimes it happens that both the jobs start together and its like one job checks for a security and if it doesnot find the security ,it creates a record in the table. Now while its about to commit,job 2 also finds that there is no security(dirty read on table)and he also creates a records. We have security_id as primary key and which is generated randomly while a record is created to the table. How can i avoid this situation. I need only one record to the table. So after these transaction the situation becomes like 2 similar records in table with 2 different security_id. Kindly suggest what can be done to avoid this.
|
|
|
|
|
|
|
|
| Re: Dirty read issues [message #637382 is a reply to message #637366] |
Sat, 16 May 2015 03:36  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote: one job checks for a security and if it doesnot find the security ,it creates a record in the table. This check must be being made on a natural key. So declare this as unique, and the second insert will then fail. Just because you have a surrogate primary key is no reason not to have natural keys as well.
And, by the way, the behaviour you are describing is "read committed", not "dirty read".
[Updated on: Sat, 16 May 2015 03:38] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Dirty read issues [message #637516 is a reply to message #637454] |
Tue, 19 May 2015 08:04 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
If you want to you can always use the LOCK TABLE command while you make your primary key. Any commit or rollback will release the lock.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
