While some people will try to explain just exactly what kind of database locking types exist, I am a big fan of the more pragmatic approach : how to avoid concurrency issues.
1. Keep a history of your data, or use versioning on your table.
If you are using a value that gets updated by another transaction within your own transaction, chances are you are supposed to keep a history of the row versions.
Edit : how to actually do this
Add a datetime field to your table called "ValidUntil", and set it to maxdate default.
In your datalayer, on update/delete, make a row copy, set "ValidUntil" of the original row to the current date, and "ValidUntil" of the new row to maxdate. Don't use a null value instead of maxdate, since it might have some performance impact on some databases regarding index usage etc...
2. Work with a staging and a production environment
Use a database for temporary storage, and use a queue to deploy your staging stuff to the production database. Using a queue, you are sure that no transactions are interfering with each other, since you can allow only 1 job at the time using the queue.
3. If there might be some row -or level locking, bring the database down
Users - at least I do - find it annoying being busy with something, and having to abort it (because of database locks, or some other stuff)
I personally prefer to see a message like this :
the database is currently down for maintenance, please try again within .. minutes.
instead of having to restart my job all over again.
4. Get latest version of volatile data
.. using a view on the available data. Things like current stock etc should be calculated on the fly while being requested.
If you are having performance issues, try using a materialized view....
5. Review your code/model
Chances are your model is not exactly correct. Check point #1
Conclusion
Being an the only Oracle DBA at our national television station, I tend to have a lot of knowledge considering databases. While the difference in database lock types might make an interesting speech, the main issue here is trying to avoid them using the tips mentioned above...
Subscribe