Category Archives: CSS

When you’re adding a color to your web page with HTML, sometimes you can just type in the name of the color. But more often than not, you’ll need to use what’s called the hex code, which is something that the browser will be able to understand. Choose a color from the list below and look to its left to get the hex code. If we wanted our background to be red, for example, we’d type bgcolor=”#FF0000″. Try it out!


Hex Code Color
Hex Code Color
Hex Code Color
Hex Code Color
Hex Code Color
Hex Code Color
#00CCFF DeepSkyBlue
#009999 DarkCyan
#009966 Teal
#009900 Green
#006600 Dark Green
#0000FF Blue
#0000CC Med. Blue
#000099 Dark Blue
#000066 Navy
#000000 Black

Web Graphics for Beginners

As an autistic, purebred Himalayan runt, my cat Rufo poses a triple threat of cuteness the whole world can enjoy. Sound like the everyday delusions of an average cat owner? Perhaps. But if Rufo is indeed a superstar dying to be born, then it’s my responsibility to help him, via the Internet. After all, what better use of the Web than as cat promotion?

At first, Rufo’s site was nothing more than a few paragraphs describing his unique kitty allure. Although a good start, mere words failed to convey the complete Rufo experience. For that, I needed graphics. Not just photographs – if Rufo was to be taken seriously as a cat celebrity, his Web presence had to look fun and professional. So I needed other collateral as well, like a logo, page banners, and graphic navigation.

My initial attempts were less than flattering. Pictures had blurry fur, the colors were pale and washed out, and the images took forever to download. So I taught myself how to create the kind of fast, sleek, and professional images that Rufo deserves. And now I’m going to share that hard-won knowledge with you. In the pages that follow, you’ll find tips, hints, and links to off-site resources or more advanced Webmonkey tutorials – everything an aspiring graphics designer needs to transform a texty site into a graphic sensation.

We begin at the very beginning: Getting images into the computer. For Rufo’s site, this meant importing photographs and finding usable graphics.


  1. Imports Are In
  2. Edits Big and Small
  3. Starting from Scratch
  4. Formats for Every Occasion
  5. Exporting We Will Go
  6. Images in Motion
  7. Uploading, HTML, Plus a Peek at Tomorrow


Imports Are In

There are two ways to approach the creation of Web graphics:You can make something out of nothing, or you can import an image from an outside source. We’ll create a from-scratch Rufo Web graphic later, but first, let’s take at the different ways you can import an existing image.

For photographs, most local film developers offer digital processing, which transforms your old-school pics into digital images stored on a disc or CD. However, if you plan to take and use a lot of photographs for your site, you need to learn the basics of digital photography. Digital cameras import images directly into your computer, bypassing the need to print and scan the photo. Plus, most digital cameras offer a preview feature, which comes in handy whenever Rufo squirms out of the picture at the last second. If Rufo doesn’t like what he sees on the preview screen, the offending image can be zapped with the push of a button.

For digitizing those cheap and cool analog photos or illustrations, you need to learn how to use a scanner. High-end scanners are great for print projects, but for scanning Web images, most inexpensive models do just fine.

Web-ready clip art is another possible source for images. Usually the collections are so extensive, you can find something for almost any situation. Several online resources offer free or reasonably priced clip art:Creativepro, Mediabuilder, and EyeWire, and Webmonkey-sister-sites Tripod and Angelfire offer their users an extensive Image Library. In addition, book and CD collections are relatively cheap and surprisingly handy.

Another great place to find graphics for the Web is the Web itself. Saving pictures from a website is as easy as right clicking on the image (ALT + click for Macs) and selecting Save, or simply SnagIt. It’s important to remember, however, that most online content is protected by copyright. To use an image from the Web, you must either first get permission from the original owner or edit the image beyond all recognition.


Edits Big and Small

After importing an image into your computer, the next step is to open the file with one of the many graphic editing software programs available to you. Adobe’s Photoshop has been the professional’s choice for years, but there are cheaper alternatives. Jasc’s Paint Shop Pro and the open source GIMP offer many of the same features and capabilities of their more expensive counterparts. And the GIFworks tool offerd by both Tripod and Angelfire is always good for some down-and-dirty image editing.

Image editing can encompass a wide range of different processes, and in the following example I’ll take you through some of the most common and useful graphic edits. For my example, I used picture of Rufo (of course) to illustrate image resizing, color correction, and cutouts. I did everything with Photoshop, but whatever image editing software you choose to work with should let you do something similar using comparable commands.


Chances are, your imported image isn’t the exact size it needs to be for your site. To resize an image, choose Image Size under the Image menu and convert the increment setting to pixel for exact resizing. Also, since monitors only display 72 dpi (Dots Per Inch), any finer resolution is lost on your computer views, so make sure the Resolution is set to 72. Once the image has been resized, you may notice a loss of resolution quality. Unsharp Mask (under Filter > Sharpen) is an invaluable tool for precisely sharpening an image into focus.

Color Correction

Another thing people often like to do with imported Web graphics is correct the color. Simple tweaking of the Contrast and/or Saturation (under Image > Adjust) can enrich even the most washed out photos. For more complex corrections, the Color Levels feature is helpful, offering both subtle and extreme color changes.



The easiest way to remove a portion of an image is with the Rectangular Marquee Tool. Simply click and drag the “marching ant” trail to surround what you want to keep of the image (to make your selection perfectly square, hold down the shift key as you work), go to Select > Inverse, then hit Delete. If your cut is more complex, begin by making a general selection either with the Marquee/Lasso tools or by color range. From there, the selection can be refined in the Quick Mask mode. Quick Mask mode enables a selection to be finely edited with the Pen and Eraser tools. Such flexibility is important in making good selections, which in turn makes for good cutouts.

That should be enough to get you started, but I encourage you to play around with the many other tools that come with image editors to see what other startling effects you can come up with. That said, editing imported images is only part of the Web graphics game. Occasionally, you have to start with nothing and create an image from scratch.


Starting from Scratch

When only original art will do – the creation of a Rufo logo, for instance – building Web graphics from the ground up is sometimes necessary. Whether you have a clear plan of what you want to create or no idea at all, there are a few things to take into account. In the step-by-step example that follows, I used Macromedia’s Fireworks program, but you could also use a product like Adobe’s Illustrator.

Getting Started

Under File, select New. A New Document window will pop up with settings for canvas size and pixel resolution. As I said before, since monitors display at 72 pixels per inch, Web graphics are limited to that resolution.

Type Casting

Creating a graphic usually begins by adding shapes and/or type to the canvas. For unique shapes, start with something simple like a circle or square, then experiment by reshaping it with the Pen tool. Combining shapes via Union, Intersect, and Punch (under Modify) can sometimes produce pleasing results.

As a general rule, you should use HTML to create text on your site whenever possible – type should only be used in a graphic when absolutely, aesthetically necessary (logos, banners, etc.). This is because the file size of graphic type slows the overall page download time, plus extensive use of graphic type can alienate people with disabilities or old-schoolers running a text-only browser like Lynx (test your site withBobby to see how it measures up). Alt text (a description included in an image tag, such as <img src=”archiveufo.jpg” alt=”The Precious Little Sweety Meow”>) can help text based Web surfers better understand graphic content and navigation.

If you choose to use type in an image, anti-aliasing may become an issue. Anti-aliasing is an effect that counters the jagged edge of rounded graphics by blending the pixels along the graphic’s edge with the background color. This blending creates an illusion of smoothness and is usually preferred in most cases. However, the added colors do increase file size and sometimes anti-aliasing can blur small type.

Finally, a diverse font collection is paramount, although it’s important to use them sparingly. There are several online resources that offer free or cheap fonts.

Browser-safe Colors

The inconsistencies between how different browsers and operating systems display colors is a nightmarish mess. To make sure images look the same no matter what a viewer uses to look at them, the limited yet relatively Web safe palette of 216 colors was born. Select the “Web 216 palette” from the list of swatches and choose your colors from amongst those provided. For the ultimate in Web color safety, try the really safe palette.


Try, Try, Again

Once you have a basic design set, the real fun can start. Experiment with different enhancing features like fills, strokes and effects. And there’s all sorts of fun to be had with Photoshop filters – the lighting effects are especially nice.


The key is to try an endless combination of shapes and fonts while remaining mindful of a few important factors, like the limited color palette. For additional support and ideas, peruse some of the various online design resources – AIGAis a great place to start.

With your from-scratch and edited graphics now complete, your now ready to place them on the Web. But before you can export anything, you have to first decide on which Web file format best suits your image. For this, you’ll need a basic understanding of the nature of computer graphics and the features that distinguish them.


Formats for Every Occasion

Whether they’re Rufo-related or not, computer graphics boil down to two basic types:bitmaps and vectors.

Bitmap (or raster) graphics are stored as a series of tiny dots called pixels. Each pixel is assigned a color, and when theyre viewed all together, they form the picture. Bitmap graphics can be edited by erasing or changing the color of individual pixels. There are many different bitmap file formats:TIFF for print; Photoshop’s PSD and BMP; and finally GIF, JPG, and PNG for the Web.

Unlike bitmaps, vector graphics are not based on pixel patterns, but instead use mathematical formulas consisting of lines and curves that make shapes. Vector graphics are ideal for illustrations, line art, and type. However, with the exception of Adobe’ Flash and Microsoft’s Silverlight formats, most browsers do not support vector graphics. As a result, vectors have to be converted to bitmap ( rasterized) before being displayed on the web. Some file formats include: EPS; Illustrators’ AI and WMF; and PICT for the Mac.

The two primary file formats for Web graphics are GIF and JPEG, although support for PNG is growing. Both GIF and JPEG formats use different methods of compression and have respective strengths and weaknesses.

The Gift of GIF

GIF (Graphic Interchange Format) is the most common Web graphic file format. GIF compression is 8-bit and has a maximum of 256 colors. As a general rule, GIFs are better suited for graphics with areas of solid or flat color such as illustrations and logos. Other advantages to the GIF format is that it lets you create transparencies, interlacing, and animations.

A transparent GIF allows one color to be set as transparent, usually a background color. All Web graphics are square or rectangular and transparencies are a way to create the illusion of irregularly shaped images.Interlaced images display a rough version of the entire image quickly and then gradually fill in the details. This affords the viewers a sense of the image before it fully downloads, a godsend to those with low bandwidths. Animated GIFs are files that contain multiple images set to display like a slide show. They work on all major browsers and require no plug-ins.

The JPEG/JPG Option

The JPEG format (Joint Photographic Experts Group) was especially designed for images of photographic quality. JPEG compression is 24-bit, which means files can contain up to 16.7 million colors. As a result, JPEGs are ideal for photographs, drawings, and any image with complex or subtle color gradations. Unlike GIFs, the standard JPEG file is not interlaced, a problem addressed by the Progressive JPEG. However, some older browsers do not support progressive JPEGs.

PNG Pong

PNG (Portable Network Graphic) is largely considered the format of the future. PNG compression can be 8-bit, 24-bit, or 32-bit, making it the ideal format for almost any kind of graphic. However, some older browsers (like IE 6) do not support the format entirely, and some newer versions still have sporadic problems. Despite some well-founded skepticism, the future still looks somewhat bright for the license free PNG.

Armed with this basic overview of graphic file formats, we are now ready to export our images for the Web.


Exporting We Will Go

Now that your image has been edited to perfection, it’s time to prepare it for online viewing. Export an image largely involves optimization techniques, GIF transparencies, and image slicing.

Optimizing a Web graphic file can be tricky. There’s a fine line between reducing file size for faster download speeds and maintaining the integrity of the image. Most of the major graphic programs like ImageReady offer a file optimizing toolbar with preview capabilities. From this toolbar, select a file format. Depending on the type of image, the format will usually be either a GIF or a JPEG.

For GIFs, experiment with the different color palettes (Adaptive, Web 216, Exact, etc.) to find the best one for your particular image. After choosing a palette, the next step is to reduce the amount of colors as much as the image will allow. Once the palette has been reduced, you may wish to experiment with the level of dithering. Dithering is a process where unsupported or eliminated colors are simulated by combining different colors from the existing palette. The result can sometimes be grainy or noisy, depending on the type of image. For extensive dithering, the adaptive palette usually produces the best results.

When exporting transparent GIFs, after optimizing, select the transparent color (usually the background color). This will sometimes produce an unseemly edge or halo around your image. Setting the matte color to match the background can help counter this effect by blending the anti-aliased pixels into the background color.

Exporting JPEGs is even easier. The amount of compression is based on a scale from 1 to 100. The lower the number, the more compressed the file and the more loss of quality. The compression requirements for each JPEG image are different, but most can get away with a level between 30 and 70.

Large images that appear on several Web pages and only change slightly on each page can often be sliced into pieces. By slicing an image, the consistent pieces are carried over to the next page in the browser’s cache. Only the smaller, swapped portion is downloaded, thus greatly improving the overall download speed of each page. Image slicing is also sometimes necessary to accommodate complex HTML designs.

You now have the know-how to import, create, edit, and export impeccable Web graphics. However, static imagery isn’t always enough. In these instances, graphic rollovers and animated GIFs can help bring a Web page to life.


Images in Motion

When used effectively, simple mouse rollovers and animated GIFs add interactive excitement to any site, and they’re easy to make.

All Over the Rollover

A rollover is an image that changes with another image when a cursor moves over it. In most cases, this change is activated by a simple JavaScript, which most graphic programs create as a part of exporting rollovers. Since rollovers are usually used to enhance navigation, our example will be an “Email Rufo” link. I used Fireworks to create this, but you can do it by hand if you’re willing to learn a little JavaScript.


Start by creating or importing the first image and draw a slice or hotspot around it. With the slice or hotspot selected, open the Object window and name the image. The program will automatically generate the name of the second frame. Then fill out the ALT text and assign the URL you’d like the image to link to or, in this case, assign a mailto.

Next, open the Frames window and add a new frame or duplicate the first one.

ruforollover2.gifCreate or import the second image within the second frame. Once the second image is roughly in place, “onion skinning” (a handy feature that lessens the opacity of each frame, allowing you to see and compare both at once) helps with final adjustments.

When both frames are ready to go, select the slice or hotspot and then open the Behaviors window. From the menu, select Simple Rollover. This will generate the JavaScript code necessary for the rollover action. Also from this menu, you can set the text for the browser’s status bar.

Export the image as usual, only this time save the HTML as well. This contains the JavaScript code and can be integrated into a larger page design with programs like Dreamweaver or cut and pasted into any HTML text editor.

Your simple rollover is now complete. They can get more complicated, but the basic principles stay the same.

Animated GIFs

Animated GIFs are similar to rollovers inasmuch as they are images with multiple frames. However, animated GIFs aren’t usually triggered by a cursor, and they’re capable of rotating through many more frames. Animated GIFs enjoy all the benefits of a regular GIF and are supported by the major browser without additional plug-ins. There are many reasonably priced programs like GIF Constructor Set for the PC and GIFBuilder for the Mac that can [/webmonkey/99/18/index1a.html produce animated GIFs] and are easy to use. For this example, I used Fireworks again to create a four-frame, animated GIF of Rufo’s archenemy, Dogbot.

Step 1) Create or import an image into two or more frames.

Step 2) Adjust and compare each image with onion skinning.

Step 3) Fireworks sets the default frame rate at 20/100 per second, but this can be adjusted for each selected frame in the Frames window, under Properties. Also, determine how many times the animation cycles with the Looping icon at the bottom of the Frames window.

Step 4) Optimize the image and then export as an Animated GIF.

Set Frame Rate
Export Animation

It’s that simple. Animated GIFs can really draw attention to a page, but they can also be very distracting. It’s usually wise to use them only when the design or content requires it.

Now all that’s left to do is upload those GIFs and JPGs to your site.


Uploading, HTML, Plus a Peek at Tomorrow

Once you have your images saved in the proper formats, all you have to do is upload them to your site for all the world to see. And all you need to do that is FTP (File Transfer Protocol) software like the free and open-source FlieZilla. Some users prefer something with a cleaner interface, like CuteFTP (or Fetch or Transmit for Mac).

Once your image is on your server, all that’s left to do is include an img tag in your HTML file, and that’s it! Finally your site has got the picture. Congratulations!

But now that you’ve managed to give your site the right image, how to make sure it stays au courant with all the latest image technology? With that in mind, let’s finish this overview with a list of a few up-and-coming image formats that you might want to keep your eye on.

Imagining the Web Images of Tomorrow

The future of Web graphics is both exciting and frustrating as browsers slowly continue to develop support for alternate file formats.

At the forefront is the PNG, which despite its relatively long history and glaring advantages is only now becoming supported by all the current browsers. Still, web graphic enthusiasts look forward to the day when PNGs completely replace GIFs altogether. More advanced formats like the MNG are still in development and may never enjoy widespread support.

As for vector graphics, Flash’s formats continue to lead the way. However, a vector standard built around XML called SVG (Scalable Vector Graphic) is currently being considered by the W3C. This has prompted graphic giant Adobe to support and develop the format.

Also on the horizon is the new language called Canvas, which promises even more vector display and animation options. By supporting both simple and complex graphics, Canvas may soon even rival Flash.

In the meantime, the workhorse GIF and the adequate JPEG will have to suffice. By recognizing the strengths and weaknesses of each format, it’s possible to produce graphics worthy of a pet site.

As for Rufo’s future, his campaign to become America’s favorite cat continues to gain momentum, thanks in part to well-crafted Web graphics. At this rate, it won’t be long before he purrs his way into the hearts of people just like you.

CSS Menus

The code here creates a drop-down menu from HTML list elements using CSS. The code was written for and referenced to in Webmonkey’s Add CSS Drop-Down Menus tutorial.


The Code

001 <html>
005 <head>
011 <style type="text/css">
013 ul {
015     margin: 0;
017     padding: 0;
019     list-style: none;
021 }
023 ul li {
025     position: relative;
027     float: left;
029     width: 100px;
031 }
033 li ul {
035     position: absolute;
037     top: 30px;
039     opacity: 0;
043 }
045 ul li a {
047     display: block;
049     text-decoration: none;
051     line-height: 20px;
053     color: #777;
055     padding: 5px;
057     background: #CC0;
059     margin: 0 2px;
061     display: block;
063     -webkit-transition-property: background-color, color, text-shadow;
065     -webkit-transition-duration: .5s;
067     -webkit-transition-timing-function: ease-in;
069 }
071 ul li a:hover { background: #66F; }
073 li:hover ul { opacity: 1;
075 -webkit-transition: opacity 4s linear;
079 }
081 </style>
085 <script type="text/javascript">
087 startList = function() {
089 if (document.all&&document.getElementById) {
091 navRoot = document.getElementById("nav");
093 for (i=0; i<navRoot.childNodes.length; i++) {
095 node = navRoot.childNodes[i];
097 if (node.nodeName=="LI") {
099 node.onmouseover=function() {
101 this.className+=" over";
103  }
105  node.onmouseout=function() {
107  this.className=this.className.replace(" over", "");
109   }
111   }
113  }
115  }
117 }
119 window.onload=startList;
121 </script>
127 </head>
129 <body>
131 <ul>
133     <li><a href="">Home</a></li>
135     <li><a href="">Web</a>
137       <ul>
139         <li><a href="#">Browser</a></li>
141         <li><a href="#">Search</a></li>
143       </ul>
145     </li>
147     <li><a href="">Monkey</a>
149       <ul>
151         <li><a href="">Eating Banana</a></li>
153         <li><a href="">Throwing Poop</a></li>
155       </ul>
157     </li>
161     <li><a href="">Contact</a>
163       <ul>
165         <li><a href="">Via Web</a></li>
167         <li><a href="">Via Phone</a></li>
169         <li><a href="">Via tin can and string</a></li>
171       </ul>
173     </li>
177 </ul>
179 </body>
181 </html>

The Solar System, Rendered in CSS and HTML

web developer named Alex Giron has created a working model of the solar system using only web standards.

It’s fully animated — though Alex takes advantage of some new CSS 3 features (border-radius, transforms and animations) and utilizes the -webkit prefix, so you’ll need to view it in Safari or Chrome to see the planets move around the sun.

Firefox and Opera users won’t see the animations, just a static CSS layout. But the hover events work, so you can mouse over each planet and learn the story of each celestial body. And yes, he included Pluto.

Manage Transactions in MySQL – Lesson 2

In Lesson 1 of this tutorial, you leaned how to approximate transaction-like behavior with MyISAM tables. In this lesson, you’ll see how you can achieve real transactions with each of MySQL’s three transactional table types: BDB, InnoDB, and Gemini. But before I get to specifics surrounding each table, I need to revisit the notion of locking.

Remember that MyISAM supports only table-level locks of two types: read locks and write locks. The transactional tables types offer more granular locking mechanisms: They can place locks on specific subsets of data. You’ll read more about each table’s locking mechanisms as I get to them in the following pages.


  1. Using BDB Tables
  2. Working with Gemini Tables
  3. Using InnoDB Tables
  4. The Right Choice for You


Working with MySQL’s Transactional Table Types

Transactional table types can apply two types of locks. These are known as shared locks and exclusive locks. A shared lock is similar to the MyISAM read lock. When a client places a shared lock on data, other clients cannot alter that data with UPDATE or DELETE statements, but they can read the locked data via SELECTs. When a client gains an exclusive lock, other clients can neither alter the locked data via UPDATEs and DELETEs, nor can they read the data with SELECTs.

Before we get to the actual code, there’s a MySQL setting I need to mention. MySQL contains an environment variable called autocommit. By default, autocommit is set to 1. When autocommit is set to 1, you cannot run transactions with any of the MySQL table types. In this mode, MySQL treats every SQL statement as its own transaction. To use groups of statements as transactions you need to turn autocommit mode off. So in all the code snippets that follow, you’ll notice that I start each with the line:

1 <code>SET autocommit=0;
3 </code>

OK, now on to the rest of the code, starting with BDB tables.


Using BDB Tables

To check if you have BDB tables available, run the following query:

01 mysql> show variables like '%have%';
03 +---------------+-------+
05 | Variable_name | Value |
07 +---------------+-------+
09 | have_bdb      | YES   |
11 | have_innodb   | YES   |
13 | have_isam     | YES   |
15 | have_raid     | NO    |
17 | have_symlink  | YES   |
19 | have_openssl  | NO    |
21 +---------------+-------+
23 6 rows in set (0.00 sec)

If the first row in the listing says “no” you don’t have BDB installed. If you’ve got either InnoDB or Gemini, that’s fine — you can just use one of those, and I’ll talk about these tables soon (but please stick around, what you learn here will be useful there). If you decide to work with BDB tables, you can either install a MySQL-Max binary or recompile MySQL from source using the --with-bdb flag. BDB tables are available for most operating systems, including Windows, Linux, and Mac OS X.

To create BDB tables, you can include type=bdb at the end of the CREATE statement. For example:

1 create table inventory(
3     product_id int not null primary key,
5     in_stock int not null
7 )type=bdb;

Or, if you wish to move data from a MyISAM to a BDB table, you can run an ALTER statement, like so:

1 <code>
3 ALTER table table_name type=bdb;
5 </code>

Note that before you run an ALTER statement like this, you should backup the table. This type of ALTER statement actually dumps the table data, creates a new BDB table, and then loads the dumped data into the table. It’s an involved process that can fail, and if it does fail you want to be protected. Also be prepared to wait a while. If you’ve got large table, the dump-and-reload process can take a bit of time.

BDB tables offer page-level locking. What’s a page? It’s a group of rows. The exact number of rows in a page varies depending on the number of rows in your tables, the operating system in use, and other factors. During a transaction, MySQL will apply a lock to a page worth of data. All other rows are unaffected by locks and ready to process whatever queries you throw at them.

Page-level locking is a vast improvement over the table-level locking used by MyISAM tables. If only groups of rows — small subsets of whole tables — are locked by a single client, that means many clients can access different portions of the table simultaneously. In fact, it’s acceptable, even commonplace, to have many clients applying various locks to different portions of a table at the same time. The simultaneous access of data by different clients is usually referred to as concurrency. In database land (where the men have hairless backs and the women shapely legs) the greater the concurrency the better.

One of the great things about transactions is that for the most part you don’t need to worry about manually applying the locks. Just start a transaction, run your SQL statements as you normally would, and the clever MySQL engine will mostly take care of the locking for you. Neat-o!

Take a look at a simple transaction in a BDB table working with the following table:

01 mysql> select * from inventory;
03  +------------+----------+
05  | product_id | in_stock |
07  +------------+----------+
09  |          1 |       24 |
11  |          2 |       47 |
13  +------------+----------+
15  2 rows in set (0.05 sec)

To see the effect of a transaction, open up two copies of the command-line client. In one, start a transaction and run a SELECT on the first row:

1 mysql> set autocommit=0;
3 mysql> begin work;
5 mysql> select * from inventory where product_id=1;

Now in the second client, start a transaction and try to update the row 1.

1 mysql> set autocommit=0;
3 mysql> begin work;
5 mysql> update inventory set in_stock=23 where product_id=1;</code>

You’ll see that the second client, the one trying to do the UPDATE, doesn’t respond. That’s because the first client has a share lock on that row. To release the lock, commit the transaction in the first client:

1 <code>mysql> commit;</code>

You’ll notice that the second client is immediately freed to run the update operation.

Take a look at the following improvement to the pseudo-code I used in Lesson 1 of the tutorial. Note the use of the BEGIN WORK-COMMIT/ROLLBACK syntax.

01 set autocommit=0
03 INSERT query into buyers table.
05 run last_insert_id() to get user_id
09 run INSERT into orders table
11 run last_insert_id() to get order_id
13 for each of the items in the order
15     get quantity from the inventory table
17     if quantity is > 0
19         insert into order_items table
21         update inventory table subtracting the ordered item
23     elseif quantity = 0
25         set error variable
27 if error variable is not set
29     update orders table with the current order_id,
31     adding the order_total
35     COMMIT
37 else
41     output error

Pretty nice, eh? Much cleaner than the MyISAM transaction-equivalent, I think. Keep in mind that you get all the ACID properties with this code if you’re using BDB tables. There is only one line of this code that doesn’t really work well and needs to be improved.

Notice that for each item in the order, I first check the quantity from the inventory table. That quantity will come from a SELECT statement, something like SELECT in_stock FROM inventory WHERE product_id=1. But in most cases, the inventory is going to change just after this SELECT is run. If there’s sufficient quantity in stock, this person is going to buy the item and therefore the quantity listed in the table will decrement by one when the transaction is complete. You’d hate to see other clients get data that will be inaccurate in a matter of milliseconds.

In a case like this, you’d be much better off putting an exclusive lock on a page containing the row in question until the transaction is complete. Remember, with an exclusive lock, all other clients are forbidden from even reading the locked rows. But unfortunately, BDB tables have no way to execute exclusive locks on pages of data. Only by instituting a table-wide write lock can you safeguard the data in the inventory table.

When revised with the table-wide write lock the pseudo-code looks like this:

01 set autocommit=0
03 INSERT query into buyers table.
05 run last_insert_id() to get user_id
09 run INSERT into orders table
11 run last_insert_id() to get order_id
13 LOCK TABLES inventory WRITE
15 for each of the items in the order
17     get quantity from the inventory table
19     if quantity is > 0
21         insert into order_items table
23         update inventory table subtracting the ordered item
25     elseif quantity = 0
27         set error variable
31 if error variable is not set
33     update orders table with the current order_id,
35     adding the order_total
39     COMMIT
41 else

The LOCK TABLES command here is a bit messy. But with the other two MySQL tables you won’t have to worry about this kind of lock.


Working with Gemini Tables

It’s difficult to talk about Gemini tables without touching on the controversy surrounding the maker of the Gemini table, NuSphere. The NuSphere corporation is currently in a legal dispute with MySQL AB, the folks who code the MySQL core engine. The issues include copyright infringement and abuse of the GNU GPL. You may want to read further about these issues before choosing the Gemini table type.

Gemini offers a more granular level of locking than BDB. With Gemini (and InnoDB, which I’ll discuss momentarily), you get the advantage of row-level locking. With Gemini, you can apply both share and exclusive locks to one row at a time. A row-level locking scheme will generally achieve higher concurrency than page-level locking, and much greater concurrency than table-level locking.

You can check if you have Gemini installed by running show variables like '%have%'. And if you don’t have Gemini, you can get an installer that includes MySQL, PHP, mod_perl and other goodies from theNuSphere website. Note that the most current release of the Gemini table seems to have some bugs running on Windows 98, so if any of the following experiments crash your NuSphere MySQL installation on Windows 98, don’t blame me.

To create Gemini tables include the type=gemini syntax at the end of the CREATE statement, and to transfer a current table to Gemini, run an ALTER TABLE query. The same caveats I mentioned for BDB apply here. For example:

01     create table inventory(
03     product_id int not null primary key,
05     in_stock int not null
07 )type=gemini;
13 ALTER table table_name type=gemini;

Unlike BDB, Gemini tables implement both shared and exclusive locks. If a thread runs an UPDATE or DELETE statement on a row, Gemini will place an exclusive lock on that row, preventing other threads from either reading or altering the locked row until the transaction is complete. If you think about it, this makes perfect sense. If a row is about to change, other threads should be protected from seeing potentially inaccurate data.

If a thread runs a SELECT statement on a row, a shared lock will be placed on that row so other threads can read the row with SELECTs but they won’t be able to alter the row with UPDATE or DELETE statements.

You can do some quick experiments with Gemini tables to see how this locking works. Assume you have the following Gemini table.

01 mysql> select * from inventory;
03  +------------+----------+
05  | product_id | in_stock |
07  +------------+----------+
09  |          1 |       24 |
11  |          2 |       47 |
13  +------------+----------+
15  2 rows in set (0.05 sec)

Open two copies of the command-line client. In the first start a transaction, then run a SELECT on row 1.

1 mysql> set autocommit=0;
3 mysql> begin work;
5 mysql> SELECT * FROM inventory WHERE product_id=1;

Now in the second client, start a transaction and try two statements, a SELECT and an UPDATE.

1 mysql> set autocommit=0;
3 mysql> begin work;
5 mysql> SELECT * FROM inventory WHERE product_id=1;
7 mysql> UPDATE inventory SET in_stock=23 WHERE product_id=1;

You’ll see that the SELECT executes immediately, but the UPDATE doesn’t execute. The UPDATE won’t execute until you COMMIT or ROLLBACK the transaction started in the first client, which releases the shared lock.

You can re-run this experiment trying an UPDATE in the first client. You’ll notice that the second client is prevented from reading the locked row with a SELECT. When a client runs an UPDATE statement, it gets an exclusive lock on that row.

When I discussed BDB tables, I pointed out that at times when inventory numbers are changing fast, you need a SELECT to obtain an exclusive lock so your users access the most up-to-date information. When you run a SELECT on the inventory table to find out the number of items currently in stock, you’re better off locking out all other threads from reading that row because that number is likely to change very quickly — an UPDATE will almost always follow that SELECT.

With Gemini tables, you can get a exclusive lock while running a SELECT by using the FOR UPDATE keywords. For example.

1 <code>SELECT * FROM inventory WHERE product_id=1 FOR UPDATE</code>

Using this added power, the shopping cart pseudo-code can be re-written like so:

01 set autocommit=0
03 INSERT query into buyers table.
05 run last_insert_id() to get user_id
09 run INSERT into orders table
11 run last_insert_id() to get order_id
13 for each of the items in the order
15     get quantity from the inventory
17     table with SELECT ... FOR UPDATE
21     if quantity is > 0
23         insert into order_items table
25         update inventory table subtracting the ordered item
27     elseif quantity = 0
29         set error variable
31 if error variable is not set
33     update orders table with the current order_id,
35     adding the order_total
39     COMMIT
41 else

This is much cleaner, and much safer, than non-transactional code. And you can do something similar with InnoDB tables.


Using InnoDB Tables

InnoDB offers row-level locking and works on a wide variety of operating systems, including most UNIX variants, MacOS X, and Windows. This table type is now included by default in most MySQL installations, so if you get current binary from, you’ll have InnoDB tables. You can check if you have InnoDB installed by running SHOW VARIABLES LIKE '%have%';.

You can create InnoDB tables by using type=innodb in the CREATE statement or by running an ALTER TABLE command. For example:

01 create table inventory(
03     product_id int not null primary key,
05     in_stock int not null
07 )type=innodb;
13 ALTER table table_name type=innodb;

The InnoDB transaction model is not quite as straightforward as the Gemini model. If you don’t understand how InnoDB implements transactions, you could end up with some serious problems with your data.

InnoDB uses what’s called a multi-versioning concurrency model. Essentially, this means that each transaction is locked to a view of the data at a specific point in time. When a thread initiates a transaction, InnoDB gets a snapshot of the data based on the exact moment the transaction started. To understand how this works, take a look at the following example. You have a simple InnoDB table:

01 mysql> select * from inventory;
03 +------------+----------+
05 | product_id | in_stock |
07 +------------+----------+
09 |          1 |       24 |
11 |          2 |       47 |
13 +------------+----------+

In one copy of the command line client, you start a transaction:

1 mysql> set AUTOCOMMIT=0;
3 mysql> begin work;

This thread now has a snapshot of the table seen above. If you were to then open another copy of the client and start another transaction, that second client would have the same snapshot of the same table. At this point, the first client runs an UPDATE statement and commits the transaction.

1 <code>mysql> UPDATE inventory set in_stock=23 WHERE product_id=1
3 mysql> commit;</code>

Even after this COMMIT, the second client will not see the results of the UPDATE. The second client receives a snapshot of the data at the moment its transaction began. Until the second client finishes its transaction (with a COMMIT or ROLLBACK), it will not be able to view the changes implemented by other threads that took place while the transaction was active.

Things get really sticky if the second client was also running an UPDATE on the same row. If the first and second clients start their transactions at the same time, they would get the same snapshots of data. The first client would run an update, WHERE order_id=3, but the second client would be unaware of the change. The second client could then run its own update, UPDATE WHERE order_id=3. After both of these clients commit their transactions, only one of the values can survive. As it turns out, the last one to COMMIT wins.

As you can see, this is potentially a dangerous situation. If you’re not careful, some of your threads could see out-of-date information.

But there are steps you can take to make sure the rows obtained by your transactions are up to date. It may not surprise you that staying current involves locking. With InnoDB, threads can apply the same types of locks that were available in Gemini.

In Gemini tables, when you run a SELECT within a transaction, a share lock is placed on the rows read by the SELECT. However, by default, InnoDB does not place any locks on rows during a SELECT. But you can let InnoDB know that you want to obtain a share lock by the adding the clause LOCK IN SHARE MODE at the end of a SELECT.

When a thread issues a SELECT with a LOCK IN SHARE MODE clause, InnoDB goes beyond the snapshot of data taken at the start of the transaction and looks for the latest committed data. If another thread is operating on that data (via an UPDATE, DELETE, or another LOCK IN SHARE MODE), the first thread won’t be able to obtain the share lock and will wait until the other thread finishes its transaction and releases its locks. By attempting to obtain the share lock and receiving the latest committed data, the LOCK IN SHARE MODE clause ensures that the data read by a SELECT will be accurate and up to date.

If you’d like to see how this works, try the following, using the same table shown above. In one client, start a transaction and then run an UPDATE statement:

1 mysql> set autocommit=0;
3 mysql> begin work;
5 mysql> update inventory set in_stock=22 where product_id=1;

Now in the second client, run two different SELECTs. The first you will run without the LOCK IN SHARE MODE clause. Try SELECT * FROM inventory. This will execute immediately, but the data is potentially inaccurate because there’s no telling if the first client will COMMIT or ROLLBACK the transaction. If you alter that SELECT statement to include the LOCK IN SHARE MODE clause, the client will not respond until the first client issues a COMMIT or ROLLBACK.

Threads can obtain exclusive locks in SELECT statements by using the same FOR UPDATE clause that is used with Gemini.

The pseudo-code shown for Gemini will also work for InnoDB tables. But be very careful when writing your SELECT statements for InnoDB. You want to make sure you’re getting the latest, most accurate data.


The Right Choice for You

At this point, InnoDB is the most logical choice for most. It offers row-level locking, is available on most every operating system, and is getting a lot of support from MySQL AB. InnoDB is also the first table type to enforce foreign key constraints. But if you’re working with an installation that has support of one of the other transactional tables, both of those will work just fine.

When you decide on the table type you wish to work with, you’ll have more technical matters to consider. You should read everything the MySQL manual has to say on the table type you end up using.

Pay especially careful attention to your logs. The transactional tables use logs to help recover from crashes. If a hard-disk failure causes a crash on your system, you could be in trouble if your logs are on the same hard disk as your MySQL data.

If you’re unhappy with the types of locks initiated by specific statements, you can adjust the locking behavior of Gemini and BDB table with the SET TRANSACTION command. I won’t cover it in detail here because the defaults discussed in this tutorial are usually preferable. Note that InnoDB tables are unaffected by SET TRANSACTION command.

Transactions are a great improvement to the MySQL database. If you’re not using them now, consider using them in the future. Because when that backhoe works it way through your power cord, you’ll be awfully glad you did.