Performance problem of 'Select for update'
Today a friend ask me a quesiton.
"I know that you talk about the 'select for update' problem long before, but could you tell me how to resolve the performance problem dued to this SQL ? we 're having performance problem right now for our production database!"
Though I have told my friend one year ago to remind him of avoiding this situation using different technique, they 're ending up with this situation!
Why use 'Select for update'?
The statement 'select for update' is an ordinary operation for locking some table data rows for later use and to prevent others to modify the same data at the same time. It is a useful operation to insure a unique and strictly continued serial number for business regulations requirement(e.g. financial transaction number in the banking industry etc).
A common flow of using 'select for update' (implemented by RDBMS)is:
- lock the data rows
- select the data rows into memory for some calculation
- write the new data back to the data rows
- Release the lock.
Why does it cause the performance problem?
From the previous description, we know that there 's only one process that could access the data when using 'Select for update', this will cause great performance problems if every or most of the transaction need to do the same operation. just image a narrow door that only allow one human entry at a time. When the number of people increase , the time for each person to pass the door increase too!
The solution
There 's no real solution for this problem. But there 're some guidelines to avoid it in the design process of the application.
First question we should ask is the neccessity of the 'select for update', not every business scenario require the use of 'select for update', for example, if the the businee require a increasing serial number but not strick progressively. then we could use Oracle sequence with cache option, the sequence with cache option could product sereval sequence at the same time, if any of the transaction fail, the corresponding sequence number is discard and this allow the concurrent access of the sequence at the same time and ensure the unique sequence number and will not cause any problem.
Even the business require strictly continued serial number, we could always devide the business into smaller sub-unit and ensure each sub-unit use its own 'select for update' for its own serial number. by devided into 2 sub-units, the business get 2X scalability, if devided into 4 sub-unit, the business get 4X scalability comparing to the only-one-serial number solution.
Recommended books:
Author: Richard Niemiec
Publisher: McGraw-Hill Osborne Media (2007)
Binding: Paperback, 967 pages
Author: Kirtikumar Deshpande, K. Gopalakrishnan
Publisher: McGraw-Hill Osborne Media (2004)
Binding: Paperback, 400 pages