This is the third day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021 “.

SQL Server has a column named RowVersion, and the previous column was TIMESTAMP, so these two keywords are synonyms in SQL Server, but timestamp is deprecated, so it is best to use RowVersion instead. In database migration, because this type is used, you need to consider how to migrate it.

  • πŸ“’ welcome to like: πŸ‘ collect ⭐ message πŸ“ if there are mistakes please correct, give people rose, hand left lingering fragrance!
  • πŸ“’ This article was originally published by Webmote and published by CSDN.
  • πŸ“’ author’s motto: life is toss about, when you don’t toss about life, life will start to toss about you, let us come on together! πŸ’ͺ πŸ’ͺ πŸ’ͺ

1. RowVersion in SQL Server

Timestamp/ RowVersion is an EF Core attribute for which the database automatically generates a new value every time a row is inserted or updated.

This property is therefore also treated as a concurrent flag, which ensures that an exception will be raised if the row being updated changes after you query the row. Refer to the previous optimistic database locking introduction.

For SQL Server, the byte [] property is typically used, which is set to the ROWVERSION column in the database.

The code is as follows:

public class Blog {
    public int BlogId {get; set; } 
    public string Url { get; set; }
    [Timestamp]
    public byte[] Timestamp { get; set; }}Copy the code

It can also be set inside OnModelCreating

internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder){ modelBuilder.Entity<Blog>() .Property(p => p.Timestamp) .IsRowVersion(); }}public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public byte[] Timestamp { get; set; }}Copy the code

Note: If you want to map to ULONG, use the HasConversion function:

modelBuilder.Entity<Blog>()
            .Property(p => p.Timestamp)
            .IsRowVersion().HasConversion<int>();
Copy the code

In SQL Server, RowVersion is an incremented column type that is only used to define data tables. Its value occupies a fixed size of 8 bytes. It is a database level unique binary number automatically generated by the SQL Server database and stored in binary(8).

1.1 Introduction to increment principle

Each Database has a self-incrementing counter, Database RowVersion, which is incremented when a user executes an insert or modify command on a table with a RowVersion field.

You can query its value using the global variable @@DBts, which is unique, incremented, and non-rollback across the database.

select @@DBTS;
Copy the code

Of course, there is at most one RowVersion field for a data table.

1.2 RowVersion Field features

  1. Each database has only one counter, so all tables with RowVersion fields have different values;
  2. The RowVersion of the database is only incremented, not rolled back;
  3. Automatically assigned by the database (insert, modify), not explicitly assigned;

2. PostgreSQL has Timestamp/RowVersion

PostgreSQL has the Timestamp type, which is a date-time field and cannot be converted to the RowVersion/Timestamp type of SQL Server directly.

The column type closest to RowVersion behavior is the PostgreSQL Xmin hidden column for MVCC management, which is automatically created by each table system and therefore does not need to be added. Of course, there are other hidden columns, such as xmax, where xmin represents the transaction number for inserting the table and xmax represents the transaction number for deleting the table.

The only drawback is that using Xmin as a row version identifier cannot distinguish between two or more changes within the same transaction. Of course, the first change within a transaction is not visible to other transactions; the only person who sees it is the transaction that changed the row itself.

The following entity definitions can be adopted in EF to support XMIN columns.

[Timestamp]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
[Column("xmin", TypeName = "xid")]
public uint Rowversion { get; set; }
Copy the code

Of course, you can also write statements in OnConfiguring.

builder.Entity<EmailAddressValidation>()
.Property(e => e.RowVersion).IsRowVersion().HasColumnName("xmin").HasConversion<int> ();Copy the code

Check the database data. OK, that’s right.

3. Summary

The RowVersion field is interesting. Did you fail it?

πŸ‘“ have seen this, but also care about a thumbs-up?

πŸ‘“ has been liked, but also care about a collection?

πŸ‘“ are collected, but also care about a comment?