SQL Server Deadlock - NOLOCK and ROWLOCK hints

Deadlock is something that will become most certainly, subject of your worries sooner or later during the lifetime of your application (or better to say database). Since MS SQL Server requires locks for SELECT, even those people who are not trying to modify database are involved in locking system. UPDATE and DELETE, obviously, requires locks. Even if you refer directly to primary key, page locks are most likely to happen in case of massive (batch) requests of same operation, because most of data will be in the same page.
Well, solution may be found in 2 little cute hints: WITH NOLOCK (for selects) and WITH ROWLOCK (for updates and deletes). With those hints you will "ask" sql server no to make any locks during selects and to lock only single row you wish to update. In short, that's it. Be careful when you are using these hints especially if your application is sensitive on data accuracy.

For further reading:
SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK
Michael Balloni
sql-server-perfomrance.com

Do you feal dirty?
Phil Scott's blog

Yahoo! Pipes has arrived

Yahoo says:

Pipes is an interactive feed aggregator and manipulator. Using Pipes, you can create feeds that are more powerful, useful and relevant.

Actually Y! Pipes is tool which gives you ability to create your own web space using variety of different online (web) services, and, at the same time, you don't need to have any programming knowledge or skills, only by using its development interface.
You "just" need to have an idea, to know who you should ask for information and to assemble it into your own unique product.
Like a shoe making process: You don't need to be a lace manufacturer nor having a sewing machine, you "just" need to have an idea how the shoe have to look like and have a pencil to sketch it.
Idea is you, public web services are materials (leather, rubber, etc) and Y! Pipes will be your glue (sewing machine) which will assemble all that.

Read more:

Yahoo! pipes
http://pipes.yahoo.com/

Yahoo! Launches Pipes (TechCrunch - Nik Cubrilovic)
http://www.techcrunch.com/2007/02/07/yahoo-launches-pipes/

Yahoo! Pipes: The Modules For Building Pipes (O'Reilly Radar)
http://radar.oreilly.com/archives/2007/02/yahoo_pipes_the.html

Yahoo Pipes (Anil Dash)
http://www.dashes.com/anil/2007/02/08/yahoo_pipes

Crystal Reports and Visual Studio .NET Application Deployment

If you are having a trouble to deploy your application which uses Crystal Reports then you need too know, that only way to do it successfuly is to make installer and deploy CR merge modules. This is not intuitive task at all, but when you aware of this procedure once, you will not have much trouble to repeat it in the future.
So, don't try to do it w/o installer (by copying dlls) 'cause it's not doable.

Tip:

In case of VS 2003 Crystal Reports 9 (bundled Version):

License key should be entered in "Crystal_regwiz2003.msm" (aka "Seagate Registration Wizard" module)

Also add:
- VC_User_CRT71_RTL_X86.msm
- VC_User_STL71_RTL_X86.msm

Detailed instruction how to do this, you can find here:
http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_deployment.pdf.asp (PDF)

Also, if you need to update merge modules:
http://support.businessobjects.com/downloads/merge_modules.asp

INSERT and UPDATE of large set of data in MySQL 4.x

When you are working in MySQL (4.x and lower) you are doomed to work w/o stored procedures, triggers, UDFs on which you used to work with, on all major database systems. So, in scenario when you need to update table with large number of cells, and each cell is represented as single row in table, you will face yourself with problems:

1. Avoid of inserting duplicated data
a) Insertion of new data (let’s say, added by entering data in cell)
b) Updating of existing data in table (changing the present value in cell)

2. Executing one query per cell will potentially cause bandwidth bottlenecks (large number queries sent from your client to database) and low performance of your application

Enough talking about troubles when we have solution:

CREATE TABLE ` MyTable ` (
`ID` int(10) unsigned NOT NULL auto_increment,
`a` int(10) unsigned,
`b` int(10) unsigned,
`c` int(10) unsigned
PRIMARY KEY (`ID`),
UNIQUE KEY `IU_ABC` (`a`,`b`,’c’)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO MyTable (a,b,c)
VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE
a=VALUES(a),b=VALUES(b),c=VALUES(c);

There are 2 crucial things to point to:

1. UNIQUE KEY in talbe creation script + ON DUPLICATE KEY UPDATE in INSERT statement
2. And VALUES (…),(…)

Let’s explain:

1. ON DUPLICATE KEY UPDATE works as it says: Do update if unique key (UI_ABC in our example) constraint is violated

2. VALUE (…),(…) is same as “select 1,2,3 union all select 4,5,6” or, better to say, “set of data” you want to insert/update


That’s it. Whatever programming language you use, you will be able to accomplish this by building query using string concatenation.

Good luck.

Reference:
http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html

Interesting to read:
http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/

Can ColdFusion "continue"?

Believe it or not, ColdFusion does not support “continue” (nor tag: “cfcontinue”).

This script demonstrates one of possible ways how you can simulate “continue” behavior:

<cfloop from="1" to="10" index="i">
<cftry>
 <cfif>
  <cfthrow type="continue">
 <cfelse>
  #i#
 </cfif>
 <cfcatch type="continue">
  <!--- ignore --->
 </cfcatch>
</cftry>
</cfloop>

Reference:
http://weblogs.macromedia.com/cantrell/archives/2003/08/living_without.cfm

Update:

Since 5th October 2009, day when CF9 has been released, developer can, finally, cfcontinue ;) Hooray. Ah, yes, "lupus in fabula", Adobe introduce cffinally as well.

Use enumartor as data source (combobox)

[C#]

enum WorkDays{
Monday=1,
Tuesday=2,
Wednesday=3,
Thursday=4,
Friday=5
}

System.Windows.Forms.ComboBox comboBox1;

//init combobox object
comboBox1 = new System.Windows.Forms.ComboBox();

//assign data source
comboBox1.DataSource = Enum.GetValues(typeof(WorkDays));

In the SelectedValueChanged event for the ComboBox.
[C#]

private void ComboBox1ValueChanged(object sender, EventArgs e)
{
System.Windows.Forms.ComboBox cmb = (System.Windows.Forms.ComboBox)sender;
WorkDays SelectedWDayEnum = (WorkDays)cmb.SelectedValue;
string SelectedWDayText = SelectedWDayEnum.ToString();
int SelectedWDayValue = (Int32)SelectedWDayText;
}

Source extended:
http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c88c.aspx#q1124q

Tomcat 5.x - MySQL Bug: "Cannot get a connection, pool exhausted"

A web application has to explicetely close ResultSet's, Statement's, and Connection's. Failure of a web application to close these resources can result in them never being available again for reuse, a db connection pool "leak". This can eventually result in your web application db connections failing if there are no more available connections.

There is a solution to this problem. The Jakarta-Commons DBCP can be configured to track and recover these abandoned dB connections. Not only can it recover them, but also generate a stack trace for the code which opened these resources and never closed them.

To configure a DBCP DataSource so that abandoned dB connections are removed and recycled add the following attribute to the Resource configuration for your DBCP DataSource:

removeAbandoned="true"

When available db connections run low DBCP will recover and recyle any abandoned dB connections it finds. The default is false.

Use the removeAbandonedTimeout attribute to set the number of seconds a dB connection has been idle before it is considered abandoned.

removeAbandonedTimeout="60

The default timeout for removing abandoned connections is 300 seconds.

The logAbandoned attribute can be set to true if you want DBCP to log a stack trace of the code which abandoned the dB connection resources.

logAbandoned="true"

The default is false.

Abstract taken from
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto

Additional information:
http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-j2ee.html#connector-j-usagenotes-tomcat

The First "Computer Bug"

Moth found trapped between points at Relay # 70, Panel F, of the Mark II Aiken Relay Calculator while it was being tested at Harvard University, 9 September 1945. The operators affixed the moth to the computer log, with the entry: "First actual case of bug being found". They put out the word that they had "debugged" the machine, thus introducing the term "debugging a computer program".
In 1988, the log, with the moth still taped by the entry, was in the Naval Surface Warfare Center Computer Museum at Dahlgren, Virginia.

It has now become a popular tradition that it was the legendary American Naval officer and mathematician Grace Murray Hopper who found the offending insect, but it's also said that she wasn't there when it happened. (Grace was a pioneer in data processing and is credited with developing the first compiler, which is a program that translates a high-level human-readable language into the machine language understood by the computer. In 1983, Grace became the first woman to achieve the rank of rear admiral in the United States Navy.)

It is also widely believed that this incident was the origin of the term "bug" itself, but this is also not the case. If you read the wording of the report carefully, you can see that the writer is really saying: "Hey, we actually found a bug that was a real bug!" In fact the word "bug" was already being used in Thomas Edison's time to imply a glitch, error, or defect in a mechanical system or an industrial process. Furthermore, "bug" was used as far back as Shakespearean times meaning a frightful object (derived from a Welsh mythological monster called the "Bugbear"). (See also The "worst" computer bug.)


Abstracts taken from:
http://www.history.navy.mil/photos/pers-us/uspers-h/g-hoppr.htm
http://www.maxmon.com/1945ad.htm