Oracle: Locking 101

Locking data is essential to prevent more than one user altering data in the database at the same time. Oracle can lock one row, multiple rows, or the whole table. Oracle attempts to lock at the lowest level possible to ensure minimal impact on the database. It is possible using LOCK TABLE to lock manually lock a complete table. I have personally never needed to use this statement and it is certainly not recommended for most applications with more than one concurrent user.

Querying the database will *never* produce a lock using Oracle, and unlike some databases locking will not stop Oracle producing consistent queries, as the query works from the UNDO tablespace. This is the technology that allows a long running query started at 1200 to finish at 1400 and show the results of that query with all the data as it was when the query was started at 1200, regardless of changes to the data during the time the query was running.

The first user to request a lock, gets the lock and other users are queued on a FIFO basis. If the design of your application requires that control is returned to the user immediately in the event of the requested data being unavailable for locking then the NOWAIT parameter can be used.

It is important to realise that all locks are released when the transaction is terminated via either a COMMIT or ROLLBACK, whether issued explicitly or implicitly.

The table below shows the different kinds of table lock modes available:

Row Share
Allows connect access to the whole table, stops users locking the entire table for EXCLUSIVE.

Row Exclusive
As Row Share, but stops other users locking in SHARE mode
(used by DML such as INSERT, UPDATE and DELETE)

Share
Permits concurrent queries but prevents any updates to the table.
(used by CREATE INDEX)

Share Row Exclusive
To query the whole table, and allow others to do so, but prevent them locking the table in SHARE mode or doing any updates.

Exclusive
Permits queries, prevents any DML.
(used by DROP TABLE)

To monitor the current locks in Oracle you can use the Enterprise Manager or alternatively query the following views:

V$SESSION
V$TRANSACTION
V$LOCK
V$LOCKED_OBJECT

You can obtain explicit locks on individual rows using the select ... for update statement:

select * from ords_data where ords_ref = ‘SJC001’ for update;

If somebody else attempts to get a lock on that row their session will either wait or if using the NOWAIT parameter control will be returned to them immediately with an Oracle Error: ORA-00054. You can trap for this within your application and bring up a message explaining that the record is locked and to try again later.

"A book is a version of the world. If you do not like it, ignore it; or offer your own version in return." - Salman Rushdie

K2 Back Pack

While at the Hunters today waiting for the service to be finished on my car, I sat browsing through one of their magazines, in the ‘gear and accessories’ section I saw a Back Pack that seems to be exactly what I’ve been after for a while.

I sometimes carry my laptop with me on my motorcycle and have often thought that there must be a higher quality, stronger back pack available that will also function as a back protector in the event of an accident. In addition I would like a stronger, more secure rucksack for use when hiking and walking in the country.

The K2 from Land Rover seems to be just the ticket. Although the dealer did not have any in stock they are available online from here. I’ve dropped them an email to check stock levels and if in stock I hope to order one tomorrow - if I could get it for the weekend that would be great :-)




"We know what a person thinks not when he tells us what he thinks, but by his actions." - Issac Bashevis Singer

My Workstation

Since my last post about my new MacPro I’ve had a few emails asking for a picture of my workstation, so for all your lovers of sexy hardware, here you go ☺



"Propriety was a rigid master, but one that must be obeyed if one wanted to keep a sterling reputation." - Lawana Blackwell

ENKript! Uploaded

I’ve just uploaded my Text Encryption / Decryption tool, ENKript!. It can be downloaded here.



ENKript! is a simple tool that allows you to input text and then encrypt it using industry strength (AES) encryption. You select you own key, a 4 to 16 character code, which is used to both encrypt and decrypt the text. The encryption key should be agreed in advance by you and the intended recipient. Once the text is encrypted you can select the email option and the encrypted text will be sent to your recipient.

Note: A 16 Character Key is recommended if you are serious about keeping the contents of your document private.

When the recipient receives the encrypted text then they can decode using the key you both agreed upon earlier. Either ENKript! can be used to decrypt the text or alternatively visiting my website - www.genesyssolutions.co.uk and selecting the ‘Online Utilities / ENKript!’ menu option will give you a page that can be used to decrypt the received text.

As email can generally be thought of as the digital equivalent of sending a postcard, ie: the contents are open and easily visible, a tool like ENKript! makes sense. Ideally you would agree in advance a different key for each of your associates (or group of associates) that you wish to communicate with in confidence.

Suggested use would be situations like mergers and acquisitions within the corporate environment, board level communication, the recipe for grandmas apple pie, in fact anything that you do not particularly wish to be visible to the outside world. In a corporate environment particularly, nothing is ever hidden from the guys in the IT Department - unless you ENKript! it ☺

ENKript also works as a command line program and can take input from a parameter file, this enables automated / batched jobs to be setup - maybe to take a text file from a secure location, encrypt it and then have it distributed as necessary. Decryption can also be automated from the command line.

ENKript! is written in .NET 2.0 and runs on the Windows platform only.

"The great French Marshall Lyautey once asked his gardener to plant a tree. The gardener objected that the tree was slow growing and would not reach maturity for 100 years. The Marshall replied, 'In that case, there is no time to lose; plant it this afternoon!'" - John F Kennedy

Roll Your Own :-)

I’ve just finished working on a System Migration project that was one of the more complex I’ve done. The original application was running on Oracle 9i on Unix. So far so good. However the documentation for the (over 1000) tables was poor to say the least and in addition the system had been heavily modified in the 20 or so years it had been in existence. The application was never initially designed for Oracle and used rule based indexing.

On to the point of the article ☺ . It can be useful in circumstances such as these to explore the data from code rather than relying purely on SQL query tools. I created a couple of tools to help with this job, one being ODBCDataTool which I wrote in an old, but effective language called Omnis7. Despite it’s age Omnis is still a fast and flexible cross platform development tool and works well even under the latest Operating Systems such as Vista.



What I required from this tool was a nice simple interface to the tables and columns within the database and then to create schemas based on these tables within Omnis. I could then switch to code and select, combine, loop and manipulate data to my hearts content. It’s been a few months since I have used Omnis and I enjoyed using it again. The application took an evening to write and worked perfectly. As I designed it to work against any database which has an ODBC driver, it will probably surface again in the future as the need arises.

"When life seems chaotic, you don't need people giving you easy answers or cheap promises. There might not be any answers to your problems. What you need is a safe place where you can bounce with people who have taken some bad hops of their own." - Unknown

If Apple Made Pocket Knives ...

Nice ☺

Thanks to John Spencer

"Time cools, time clarifies; no mood can be maintained quite unaltered through the course of hours." - Mark Twain

Number Generator Uploaded

Here’s a little program I wrote for fun a few years ago. It runs under Windows and randomly selects six lottery numbers. 10% of all lottery wins payable to me ☺



"Every time you don't follow your inner guidance, you feel a loss of energy, loss of power, a sense of spiritual deadness." - Shakti Gawain

ViewCSVTAB Uploaded

I have just uploaded ViewCSVTAB to my website. It can be downloaded here. ViewCSVTAB is a very simple utility that will open a Comma Separated (CSV) or Tab Delimited (TXT) file and allow you to quickly view it to the screen, and search all columns for specific data. Included in the Zip Archive are versions to run natively on Mac OSX, Windows and Linux.

"No great improvements in the lot of mankind are possible until a great change takes place in the fundamental constitution of their modes of thought." - John Stuart Mill

Unix 'Man Pages' Widget

It’s not always easy to remember the parameters and format of each Unix command, especially when you don’t often need to use them. Each Unix system is documented by ‘Man Pages’, each page representing the complete details for a single Unix command. I’ve found a freely available Dashboard Widget that installs on your Mac and gives you the necessary information within seconds: http://www.interdimensionmedia.com/widgets/nixmanual/index.html
Try it, its great ☺

"Change is the process by which the future invades our lives." - Alvin Toffler

New Mac Pro :-)

It was time to invest in some new hardware. Dual core is all well and good but I was becoming increasing frustrated by a lack of Memory and CPU when attempting to run multiple VM’s, especially whilst querying large databases and running everyday applications at the same time. I decided to upgrade my main Workstation to something powerful enough to fulfill my needs for the next three years and that could be upgraded easily. I settled on one of the new Mac Pro’s with the following spec:

Two Quad Core Intel Xeon ‘Harpertown’ 2.8Ghz Processors
6 Gb RAM
500Gb 7200 SATA Primary Drive
1 Tb 7200 SATA Secondary Drive
30’‘ Apple Cinema LCD Monitor
16 x Dual Layer DVD Writer
Airport Extreme WiFi Card
ATI Radeon HD 2600 XT 256Mb Graphics Card
Applecare Protection Plan

The immediate benefit of this specification is that I can run several Windows Virtual Machines at the same time without any performance or memory issues, this saves time and frustration as it allows me to do more simultaneously. Have a look at the screenshot, it shows a Windows XP VM (itself running an IP Camera Application) and a Windows 2003 Server VM with Oracle running at the same time as several native Mac applications including a Digital TV Application, iTunes, Mail, Firefox Etc. As you can see from the Activity Monitor and the Menu Meters at the top of the screen it manages all this without breaking a sweat.



I can see this configuration providing a significant productivity boost ☺

"Hope begins in the dark, the stubborn hope that if you just show up and try to do the right thing, the dawn will come. You wait and watch and work: You don't give up." - Anne Lamott