How to implement a timestamp using datetime field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to implement a timestamp using datetime field?

Author

Topic

jfmenard
Starting Member

18 Posts

Posted - 2005-07-08 : 10:18:55

The Microsoft timestamp field changes its content whenever row content changes. However the timestamp value is binary and do not contains time information (such as day, minutes, hours). I would like to know how gurus implements the equivalent functionality using a datetime field. The solution should be enforced within the database so that any update is detected (either from an application or from Query Analyzer).

This one looks easy, but I see pitfalls for every solution I can think of...

Thanks,

J-F

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-08 : 10:35:35

try the getdate() default value, in your table design or do a select getdate() in QA, It returns 2005-07-08 15:32:45.983 for me, which i in turn use my web page to format the output

Afrika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-08 : 12:18:45

Create a default on the timestamp column with a value of getdate(). That will take care of inserts.

Create an update trigger on the table that updates the timestamp column with getdate() for the rows that match the INSERTED table.

CODO ERGO SUM

jfmenard
Starting Member

18 Posts

Posted - 2005-07-08 : 13:05:06

quote:
Originally posted by Michael Valentine Jones

Create an update trigger on the table that updates the timestamp column with getdate() for the rows that match the INSERTED table.

I tried a trigger like this:

CREATE TRIGGER Trig_MyTable_Update ON dbo.MyTable FOR INSERT, UPDATE
AS
IF @@ROWCOUNT=0 RETURN
UPDATE U SET UpdateDT = GETDATE()
FROM MyTable U INNER JOIN INSERTED I ON U.Id = I.Id

But this causes deadlocks when there are multiple updates from different connections on the same row.

Just to be sure, I made a simple .NET application that start 2 threads that each repetedly update the same row in MyTable using this statement:

UPDATE MyTable SET Field1 = 123 WHERE Id=1

It takes a few seconds and a deadlock appears.

My understanding is:
- Thread-1: Call Update on a row.
- Thread-2: Call Update on same row, but waits that Thread-1 finishes.
- Thread-1: Trigger gets called to update same row, waits that Thread-2 finishes.
- Sql Server detects deadlock and terminate one of the queries (let say Thread-2).
- Thread-2: Aborts and raise an error.
- Thread-1: Completes trigger Update statement.
- Thread-1: Commits main Update and terminates.

But nobody confirmed my assumptions.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-08 : 13:55:34

It sounds like you application is doing a poor job of handling transactions. For example, holding a transaction open while your application is waiting for user input or failing to commit a transaction.

CODO ERGO SUM

jfmenard
Starting Member

18 Posts

Posted - 2005-07-08 : 14:17:13

quote:
Originally posted by Michael Valentine Jones

It sounds like you application is doing a poor job of handling transactions. For example, holding a transaction open while your application is waiting for user input or failing to commit a transaction.

Nope. That's not the case. Here are the steps done in the thread loop in my sample application that illustrate the deadlock:
1) Open a new connection with database
2) Prepare sql command (UPDATE MyTable SET Field1 = 123 WHERE Id=1)
3) Execute command
4) Close connection

So as you see, there is no transaction kept open (other than any implicit one created by the connection) and there is no wait of any kind. These steps are in a while loop, running in 2 or more threads and the deadlock appears after few seconds.

I am astonished of the results I see.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-08 : 14:58:10

Maybe you should use a transaction to keep this from happening. Wrap your update in an explicit BEGIN TRANSACTION and COMMIT, so that another process cannot update the same row until you have committed the transaction.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources